1 of 1 people found this helpful
Can you get the Excel sheets in the same workbook? If so you should be able to use joins or custom sql to return only what you want.
Thanks Alex, seems valid and I had thought it too...
The problem is when you are managing two different connections: Imagina a database in Oracle with the same data of the first Excel file, then you want to add the second Excel file as datasource and exclude the records which appear then...
If you want to keep only the records that do not match, then you can use a two step process to remove:
- Create a boolean calculation using the ISNULL Function (ex: ISNULL([Field1] = [OtherTable].[Field1])) Where the true condition indicates a null value.
- Since boolean filters sometimes don't work, you can then write a CASE statement (ex: Case [BooleanIsNull] When True Then 1 Else 0 End) and filter on values with a minimum of 1.
This should keep only the records that appear only in the first file.
I did it blending the superstore sales and coffee data sets, actual calcs below
ISnull(ATTR([State])=ATTR([Sample - Coffee Chain (Access)].[State]))
Case [NullKeeper] When True Then 1 Else 0 End
Thanks for the answer.
I'm trying to create the ISNULL PreCalculated Field. F1_1 is the ID of the first file and F1_2 is the ID of the second Excel file. I am getting an error with the following syntax:
I get the error highlight on the equal symbol, saying: "It is not possible to mix aggregated and non aggregated fields to the function"
What I'm doing wrong?
Great! It works flawless.
Sorry for the newbie question but... I have the new meassure, how I apply it? How do I check that I want only to keep the true values? (I can't set the function as a filter...).
Drop the measure into the filters shelf and select the "at least" tab. Drag the at least slider to 1 (and the highest number you should have in there is 1).
You can check the filter by adding the F1_1 field to a viz, drop the F1_2 field next to it. You should have all Null values in the F1_2 field if the filter is working correctly.
Thanks! The "problem" is that I'm trying to drop it inside the Filters tab but I can't... Tableau doesn't allow it.
I've solved the problem putting the Calculated field in the rows section, next to the ID field. Afterwards, I've clicked in the results on the results that I want to hide (in this case: False) and then I have the list filtered.
Thanks again for your help! very useful!