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

    Combining

    hina.waheed.0

      Hello,

       

      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!

          Joshua

          • 2. Re: Combining
            hina.waheed.0

            Thank you for this! So helpful.

             

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

            • 3. Re: Combining
              Joshua Milligan

              Hina,

               

              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,

              Joshua

              • 4. Re: Combining
                hina.waheed.0

                Attached is the mock data

                • 5. Re: Combining
                  Joshua Milligan

                  Hina,

                   

                  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:

                   

                  Merge.gif

                   

                  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