If you don't particularly care about the order in which the fields are evaluated (that is, you'll accept any non-null value, even an arbitrary one if two of the fields have conflicting values), then you can just Ctrl+Click each field in the Profile Pane and then select Merge Fields. That will give you a single field with the values merged (nulls filled in by the first non-null found in one of the other fields, but the order selected by Tableau Prep - I think alphabetic order of field names).
If you want to control the order in which the fields are evaluated, then you can write a calculation like this:
IFNULL([Admission Date - BPA],
IFNULL([Admit Date - Science],
IFNULL([Admit Date - English], [Admit Date - Math])))
For each records, that will give you the first non-null date found in BPA, Science, English, or Math (in that specific order).
If you want to find the earliest non-null date, you could change the calculation to:
MIN([Admission Date - BPA],
MIN([Admit Date - Science],
MIN([Admit Date - English], [Admit Date - Math])))
Or change MIN to MAX for the most recent non-null date.
Hopefully that gives you some options!
Thank you for this! So helpful.
But to start with should I keep my full outer join?
That's hard to say without seeing the data itself. Even a mock-up of the data and structure you are using would be helpful. Do you have anything you can share?
1 of 1 people found this helpful
Thank you very much! That's helpful.
Here's the approach I would take using a Union to bring it all together, an Aggregate to find the first date per account number, and then joining that back into the flow so you can get the subject:
The final flow looks like this:
where I've re-purposed the Table Names field to be the subject.
I've also attached the flow so you can take a look at details!