5 Replies Latest reply on Feb 11, 2019 1:54 PM by Joshua Milligan





      I have 4 different data sources being joined with full outer joins. I want to make sure there is an admission date for each person, including unmatched rows. In the final cleaning step, I want to combine the four admission date columns into one by using the other columns to fill in the null values.

      Columns are-

      Admission Date- BPA

      Admit Date- Science

      Admit Date- Math

      Admit Date- English


      I have been using And/Or functions but I want to use something more efficient in tableau prep. How can I do this? Would I need to union the data?

        • 1. Re: Combining
          Joshua Milligan

          Hi Hina!


          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!


          • 2. Re: Combining

            Thank you for this! So helpful.


            But to start with should I keep my full outer join?

            • 3. Re: Combining
              Joshua Milligan



              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?


              Best Regards,


              • 4. Re: Combining

                Attached is the mock data

                • 5. Re: Combining
                  Joshua Milligan



                  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!

                  Best Regards,Joshua

                  1 of 1 people found this helpful