How to compare multiple string colums by giving them integer value ?

[Broly]

Hi all,

i want to compare 3 string columns named source 1, source 2 and source 3 as below :

 if at least one value from these 3 columns is different from the other ones, I want to create a column and mark it as "to be defined".

if all the values are the same (equals) I want to copy the value in the new column.

If a value is empty and the other ones are equal (line 4 for example) I want to copy the equal value to the new column.

finally, if all values are empty I want to mark it as "to be defined".

is it possible to do that dynamically ?

Thanks in advance

[shreyanshrathod]

Hi @Broly ,

Looks like couple of IF ELSE statements should help you out here.

E.g. IF Source1 = Source2 and Source2 = Source3 then Source1

elseif isnull(source1) and Source2 = Source3 then source2

else "to be defined"

endif.

You will need to cover all possible cases in your IF ELSE statements and I believe you should be able to achieve the desired result.

Regards,

Shreyansh Rathod

[Luke_C · Accepted solution]

Hi @Broly 

As @shreyanshrathod mentioned you could do an IF statement, however you could make this dynamic by transposing the data and running some checks.

1. I transposed the data and filtered out empty records
2. Then I did a count to see how many distinct values there were for each name. 
3. If there's only 1 distinct value, it's standard. Else it's to be defined
4. Join these back to the main data. Records with all null values will drop due to the filter earlier, so I add 'to be defined' and union it back. 

[Broly]

Works fine, thank you for your help !