7 Replies Latest reply on Feb 12, 2020 1:41 AM by Jason Henderson

    Compare records in Tableau Prep

    Jason Henderson

      I apologize in advance for the lengthy description...

       

      So, I have a set of data with the below basic columns:

       

      • ID
      • Name
      • Note Date
      • Note Type (possible types are "Referral" and "Response")

       

      I have the same source feeding in twice with one filtered to only include "Referral" note types and the other to include only "Response" note types.  I am then joining these where ID matches and Note Date of Referral is less than or equal to Note Date of Response.  This is working fine for the most part but I am getting records where there are 2 "Referral" note types with different dates dated before the earliest "Response" note type. 

       

      This is producing a data-set with the below columns:

       

      • ID
      • Name
      • Note Type (this one is always a "Referral" type)
      • Note Date - Referral
      • Note Type 2 (this one is always a "Response" type)
      • Note Date - Response

       

      I now need to work out the number of days between Note Date - Referral and Note Date - Response.  (I should add that there isn't always going to be a corresponding "Response" for each "Referral" and there may also be more than one "Response" on or after any given "Referral".  I can sort the multiple "response" records, that's not a problem)

       

      The calculation is simple for that and returns what is, technically, the correct value.  However, there is a scenario where it returns a false value.

       

      Say there are referrals for the same ID on both 01/04/2019 and 23/05/2019 but the only response is dated 24/05/2019.  In this instance I get a two rows for the two referrals, which is fine, but the number of days for the 01/04/2019 one returns a high value and the 23/05/2019 returns the more realistic value of 1.  If there was only the one "referral" i would potentially see a higher value, which would be correct.

       

      My question is can i get Prep to identify where there are more than one referral before the earliest response and return a null value?

        • 1. Re: Compare records in Tableau Prep
          Jonathan Drummey

          Hi,

           

          First of all we ask for sample data and a packaged flow so it's easier to see what's going on. See Packaged workbooks and flows: when, why, how for more details. I started writing a response but realized there are multiple layers of complexity here depending on your data and if I could see the data then I wouldn't have to anticipate all the possibilities.

           

          Jonathan

          • 2. Re: Compare records in Tableau Prep
            Jason Henderson

            Hi Jonathan, thanks for your reply.  I will knock together a dummy data set and packaged flow as soon as I get a moment and post it here for you to take a look at.

            • 3. Re: Compare records in Tableau Prep
              Jonathan Drummey

              Thanks, Jason! Here are the main questions I had:

               

              - Can there be more than two referrals for the same ID?

              - Is it possible for an ID to have multiple referrals on the same day?

              - Is it possible for an ID to have referral(s) with no corresponding response date?

               

              Jonathan

              • 4. Re: Compare records in Tableau Prep
                Jason Henderson

                Hi Jonathan, see below the answer to your questions:

                 

                - Can there be more than two referrals for the same ID? Yes

                - Is it possible for an ID to have multiple referrals on the same day? Yes but it shouldn't happen

                - Is it possible for an ID to have referral(s) with no corresponding response date? Yes, these are classed as open referrals and I would like to see the referral listed but null values in the response columns

                 

                Thanks in advance

                • 5. Re: Compare records in Tableau Prep
                  Jason Henderson

                  Hi Jonathan, I have attached here a packaged flow and the sample data for you to take a look at.  There are no actual names in the data but it gives you the gist of what I am working with.

                  • 6. Re: Compare records in Tableau Prep
                    Jonathan Drummey

                    Hi,

                     

                    I looked at the data, there are some issues. I’ve picked out representative IDs, there are multiple instances of each situation described here:

                     

                    1) ID #530 has 6 referrals and 2 responses. Should I assume that response rows go with the most recent referral row?

                     

                    2) ID #17 has two referrals and one response, all on the same day. How should this be handled?

                     

                    3) ID #1362 has two referral rows and two response rows on the same day. How should this be handled? How do we match a referral to a response when there’s no other “this goes with that” information?

                     

                    4) ID #2122 has 4 responses and one referral, how should this be handled?

                     

                    5) ID #985 has 2 responses on 2019-04-28 and no referrals at all, how should this be handled?

                     

                    Jonathan

                    • 7. Re: Compare records in Tableau Prep
                      Jason Henderson

                      Hi Jonothan, thanks for the quick reply.  I have added my comments to your questions below.

                       

                       

                      1) ID #530 has 6 referrals and 2 responses. Should I assume that response rows go with the most recent referral row? Yes, the response that would correspond with a referral should always be the first one recorded on or after the referral date

                       

                      2) ID #17 has two referrals and one response, all on the same day. How should this be handled?  This should be shown as only one referral with the response alongside it.

                       

                      3) ID #1362 has two referral rows and two response rows on the same day. How should this be handled? How do we match a referral to a response when there’s no other “this goes with that” information? If the referral "Note Type" is exactly the same, ideally it would only show one referral and response.  If the referral Note Type is slightly different then I would ideally like to see both of them, each with a response (even if they are all on the same day)

                       

                      4) ID #2122 has 4 responses and one referral, how should this be handled? Again, this would go down as one referral with the first response on or after the referral date and ignore anything after that.

                       

                      5) ID #985 has 2 responses on 2019-04-28 and no referrals at all, how should this be handled? Generally we ignore any responses where there are no preceding referrals so just exclude those responses where there are no referrals on or before the response date.

                       

                      I hope this helps

                       

                       

                      Jay