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.
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!
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.
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.