5 Replies Latest reply on Nov 22, 2016 10:02 AM by Chuck Stevens

    Data Prioritization

    Alex Marlow

      Hello Everyone,

       

      I am currently working on a project with a team member and we have two Excel data sources. Both spreadsheets have the same information listed within; however, one data source has more information than the other. Although, the data source that has less information has data that is more up to date than the other source.

       

      What we want to be able to do is show the data from the larger data source, but have data from the smaller data source prioritized when data fields match between the two spreadsheets. Joining will not work because when performing a join, only data that is in both spreadsheets will be shown. That does not work for our end goal because we want all data to be shown just some data have a higher priority than the other.

       

      Does anyone know of a way to prioritize one data source over another, but show all data from both sources?

       

      Thank you!

        • 1. Re: Data Prioritization
          Chuck Stevens

          If you could put both sheets in the same file....try unioning them.  This will create a derived dimension based on the tabname called Table Name. Then when you want to get the prioritized data, create a calculated field based on table name such as - Min([table name]).  Use Max if the tab you want to have priority is bigger in the sort. Then include  this calculated field in you Viz along with whatever dimensions you are using.

          • 2. Re: Data Prioritization
            Alex Marlow

            Hi Chuck,

             

            We were able to do the Union, our only issue now is working on the calculated field.

             

            We are doing the calculation based on this IF statement:

            IF [Table1] == [Table2] THEN [Table1 Value]

            ELSE [Table2 Value] END

             

            I am afraid I cannot post the actual workbook as I am unable to share the data. Table1 is the smaller table (which should have priority) over Table2.

             

            With this IF statement our result is just the values from Table2 and it does not take the Table1 values into consideration.

             

            Any ideas on this?

             

            Thanks for your help!

            • 3. Re: Data Prioritization
              Chuck Stevens

              create a calculated field with just min([table name]).  Drag this up to columns or rows with your other dimensions.  Your if statement will never evaluate to true since it is looking row by row and every row only has one table name value.

              • 4. Re: Data Prioritization
                Alex Marlow

                Hi Chuck,

                 

                I wanted to elaborate a little bit more on that IF statement because after trying the min([table name]), it turned out to be unsuccessful.

                 

                With the IF statement, it was searching to see if two of the columns in Excel matched (basically checking for 2 of the same numbers). If they matched then we would want to select a value from Table 1 for that value since that value is more up to date than the value from table 2. If there was not a match then that information is not available in one Excel worksheet due to one table (table 2) having more information than the other. I do believe that you are correct when you say that the IF statement would always validate to true for one table because that is what the results were showing in Tableau.

                 

                I think we are close on this, but still missing the complete logic that would prioritize the data from one table over another.

                 

                Thanks for all your help and please let me know if you have any other ideas. 

                • 5. Re: Data Prioritization
                  Chuck Stevens

                  See if this helps you.  I filter out based on the index I create.  Without seeing your data or workbook, I am just guessing.