I am not really sure how to ask this, but I have tried asking Uncle Google and searching the forums for an answer. I have a Prep Flow mostly built, but I have one more step I would like to add. I have data where there are 3 columns. In certain instances, I have rows where the first 2 columns share the same information, and they only differ in the 3rd column. In those instances, is it possible to add a 4th column and 'pivot' the field from one row to another as seen in this mock-up below? And in instances where no data is 'pivoted', the newly created 4th column remains blank? I appreciate any assistance you can provide!
Hi @Aaron Berenter
It's messy, but I think it can be done.
Start by concatenating [A] and [B] in a new field called [A+B]:
[A+B]
|1|2|
|4|5|
|1|2|
(I've created a string field using the "|" element to separate the values)
You can delete fields [A] and [B].
Now follow the advice one @Don Wise gave me for reverse pivoting (Don-- do you have a link?)
+ Generate the RANK of [Column C] for every combination of [A+B]
+ Do a reverse pivot of rows to columns: at this point you should have two new columns: [1] and [2]
+ Rename [1] to [Column C] and [2] to [Column D]
+ Split [A+B] into two columns and rename: this should restore A & B.
Please try this out on a SMALL scale and see if it works :D Check your row count and make sure you have fewer rows afterwards than before-- it's possible this will add the column you need but still leave rows duplicated.
Michael Hesser (Tableau Forum Ambassador)
If this response has answered your question, kindly click "Best Answer"
