A source dataset has 10 columns. A new column, say 11th column is the concatenation of 6 columns in the source separated by '_'. I just want to have a check column to validate that the 11th column is generated by using those 6 columns in source only.

[Trifacta_Alumni]

Hi @SRUTHY S?,

If you know the format your 11th column should have (e.g. xxx_xxx_xxx_xxx_xxx_xxx for 6 strings separated by '_'), you could try the MATCHES function to check that all fields in this column follow the correct format?

You could also use the FIND function to check that each of the 6 columns you want concatenated in the 11th column are present, and that each of the other 4 columns you do not want in the 11th column are not included in the string?

Do either of these help at all?

[41698a0ca29453be25d2]

Thank you😊 .

Format for FIND function: find(input_string,string_pattern,[ignore_case], [start_index])

Could you please help me on how to provide the 6 column names in the above mentioned format?

[Trifacta_Alumni · Accepted solution]

Hi @SRUTHY S? ,

Sure. If you use the following function, it will do a case-insensitive search through the string in column_11 for the string in column_1: find(column_11, column_1, true,0).

You will need to re-run this step 6 times- once for each string check (e.g. find(column_11, column_1, true,0), find(column_11, column_2, true,0), find(column_11, column_3, true,0)...).

There may be a more efficient solution to this problem but with FIND you can only search for one pattern at a time.

[41698a0ca29453be25d2]

Thank you for your response.😊