how to check one field exists in other more than one fields group?

[WuCong]

as you can see ,I have a field merge_ae and other fields dsae1,dsae2 and dsae3. I want to get the result the merge_ae exists in dsaeX and not exists daaex. I have no idea for the second and third row why the result is null ,I want to get false.

[Trifacta_Alumni · Accepted solution]

Hi Wu Cong,

Thank you for reaching out to us.

The second and third rows in the example above result in a null value for the Flag column because "DSAE2" has "null" values.

In the example below, "Route3" is null and hence the "Flag" column has a "null" value instead of "false" after using IN.

The second parameter in the IN function needs to be "array literal of values to search." If we convert the null value in the column "DSAE2" to an empty string before using the IN function, we will get true and false results.

In the example below, we add a step to transform "null" values in the "Route3" column to an empty string before using IN.

Alternately, we can also use IF to compare, as shown below. In this case, we do not need to transform the null values in the "Route3" column before comparing.

I hope this helps!

Thanks,

Rachana