Tableau -Calculated field for matching values based on criteria in a different column

Question

I am trying to create a calculated field based on three conditions .

I have two columns names " source system names" which has only two values 302 and 202 only and other column name "unique ID have different numerical values .

I want to create a calculated field which will display the data as shown in "calculated field display " column. I need some help to write a logic on this .I have not done something like this before in tableau .Can someone help me out .I have around 0.5 million rows so need a logic that would work for this size of data .

Please refer attach image for a clear picture of what iam doing .

202 RECORD ONLY - when unique ID does not exists in row having source system as 302 .(example row 1,3 and 4)

302 RECORD ONLY - When unique ID does not exists in row having source system as 202.(example row 9,11 and 12)

EXISTS in both 302 and 202 - when unique ID is same for source system names 302 and 202 .(2 and 10 , 5 and 8 and 6 and 7)

Kindly help me to create a calculated field with this logic .

I tried CASE and IF function ,but i was not able to do it .

for easy understanding refer the 4th column in the attached imageenter image description here

Tom Tom · Accepted Answer · 2019-08-07T16:30:20

If the 'Source System Names' has only 2 values you could create the following calculations to do what you want (assuming all the fields are strings, if they're integers this will still work with some simple changes):

Max: Source System Name

{FIXED [Unique ID]: MAX([Source System Names])}

Min: Source System Name

{FIXED [Unique ID]: MIN([Source System Names])}

Calculated Field Display

IF [Min: Source System Name] != [Max: Source System Name]
    THEN 'Exists in Both 302 and 202'
ELSEIF [Max: Source System Name] = '202'
    THEN 'Exists in 202'
ELSEIF [Max: Source System Name] = '302'
    THEN 'Exists in 302'
ELSE
    NULL
END

Hopefully that helps!

Tableau -Calculated field for matching values based on criteria in a different column

1 Answers