Hey Norbert, thanks for the quick response!
I'm not seeing any attachments (except the screenshot) in your reply. Did you possibly forget to attach it? or maybe you are referencing another post when you say "the original thread"? Or is it here and i'm just not looking in the right place?
Unfortunately i don't think this solution will work for me. You may not have noticed but I was actually hoping for a solution i could use within tableau prep, and while i'm not apposed to using desktop if thats the only way, i'm not quite sure how to export this data to a csv "as is" (see screenshot below).
That is what i got when trying to "export data". I tried also exporting a "crosstab to exel" (i'm not positive what this does but i thought i'd give it a try) however it produced an error:
just to clarify, as the final result is being used to import tables to a web app we need to produce a csv that reads as shown in my original post. Simply being able to view it in tableau desktop is not enough.
I would further REALLY like to be able to do this in tableau prep as that way i can join the data back with the rest and then from that point on this whole process can be performed automatically, however if that is not possible and it is indeed possible to somehow export the corrected data with tableau desktop that would still be helpful.
Thanks for all your help so far,
1 of 1 people found this helpful
For sure it' typical operation which should be done in an ETL tool like Tableau prep. I don;t have Tableau prep so I can't help you on that one.
Would like to ask Ken Flerlage to the table.
Ken Flerlage Could you help Calder out on this one
Upfront. Thanks a TON
While the requirement is simple, required flow is not.
Please refer to the attached packaged flow file and let me know if you are having issues. Here is a quick summary of steps in the flow:
- Create ROW IDs per group so that we can use it to pivot the data
- Convert Row IDS to Field Names(F1, F2,F3,etc)
- Pivot Rows to Columns using Field Names created above
- Use a dummy file to make sure that Filed Names are always consistent. I used 5 Fields here, but you can use as many as you need. This is required so that our calculation in next step will know all the field names in the flow
- Enter below calculation to concatenate the child_names and clean up the unused fieldnames(F1,F2,F3,etc)
STRING_AGGR.tflx 13.7 KB
Looks like Jose beat me to it!
this is awesome, thanks!
Maybe i'm just getting greedy here, but is there any way that we could do this so that it did not require any adjustments in regards to the amount of rows its given? Or would the solution to that be to just set this up (the calculated row and the dummy data rows) to work with a higher number of rows than i would ever expect to occur (currently the largest number we have in our data is 9, i could see it getting up to maybe double that but not more)?
> but is there any way that we could do this so that it did not require any adjustments in regards to the amount of rows its given
in addition to the fact that some of the calculated rows (and that dummy data set) are configured to a specific number of rows, when i added some new data and then tried to refresh the pivot i got the following error
and only a few of the rows were added. To get it to work i had to delete them and then add them again. My current assumption is that this is a bug with tableau that we can't really do anything about....?
In any case, thank you all so much! This is my first time using the community forums here and its definitely one of if not THE best I have ever used! If this is the farthest we can get let me know and i will mark this as the correct answer.
The only change to Jose's amazing solution that I might suggest is to adjust the Dummy Rows so that the Union occurs prior to the Pivot. That way it can define all the expected values for the pivot to avoid the error you are getting when new values appear. The new flow looks like this:
and the dummy rows are simply all the possible Row IDs you could want (or expect):
That way, you can add them all to the pivot once and not worry as long as you don't have more than N values (where N is the number of rows you predefined). The final calculation gets a bit larger too:
F1 + IIF(ISNULL([F2]),"",","+[F2]) + IIF(ISNULL([F3]),"",","+[F3]) + IIF(ISNULL([F4]),"",","+[F4]) + IIF(ISNULL([F5]),"",","+[F5]) + IIF(ISNULL([F6]),"",","+[F6]) + IIF(ISNULL([F7]),"",","+[F7]) + IIF(ISNULL([F8]),"",","+[F8]) + IIF(ISNULL([F9]),"",","+[F9]) + IIF(ISNULL([F10]),"",","+[F10])
Hope that helps!
STRING_AGGR_JM.tflx 14.6 KB
Thanks Joshua for fine tuning the flow. I didn't consider that scenario.
1 of 1 people found this helpful
I can't think of a way to get this done without using a dummy file to fix the column names. Joshua's revised flow is definitely an improvement, but it still requires a dummy file.
If your largest event size is 9, I would probably create a dummy file with 50 fields and call it good. If you want to be extra careful, you can set up an email trigger to notify you when the event size exceeds 50.
this works perfectly, thanks so much!
ok sounds good! the dummy file should still be fine i think. Thanks so much for getting us most of the way there!