Case Statement or Formula for creating a flag

User2021
User2021 Member
in Magic ETL

I have to create a flag in a dataset where

(IF columns A.a and B.a = A.b and B.b ) then I have to check cols A.c = B.c or A.d = B.d

My Data is like

image.png

Can someone help me in case statement. The one I have is not showing correct results. First two columns have to match to figure out discrepancies in next cols. If cols match it is 1 and if c and d cols are not matching then its 0.

Comments

  • GrantSmith
    GrantSmith Coach 
    CASE WHEN `A.a` = `B.a` AND `A.b` = `B.b` THEN
  CASE WHEN `A.c` = `B.c` AND `A.d` = `B.d` THEN 1 ELSE 0 END
END

    This will return NULL if A.a and A.b do not equal B.a and B.b resepctively.

    1 if c and d match and a and b match

    0 if they don't but a and b match

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • mhouston
    mhouston Contributor

    @User2021 are you saying that if A.a = B.a and A.b = B.b then check if either A.c = B.c or A.d = B.d? If so, I think this is the case statement logic you want:

    CASE

    WHEN (A.a = B.a and A.b = B.b ) THEN (CASE WHEN A.c = B.c THEN 1 WHEN A.d = B.d THEN 1 ELSE 0

    END)

    ELSE 0

    END

Categories