Data cleansing - Create a new column

[Anonymous]

Hello Community

a field (Order) looks usually like this: 1234567, but sometimes it contains 2 orders connected via '+': 1234567+7654321

I need to create an editedOrder Column and a flag column when this is the case:

An imaginary syntax would look like this:

IF ORDER contains '+' then

EDITED_ORDER.Value = Left(ORDER;7)

AND

MIN2ORDER_FLAG = 1

ELSE

EDITED_ORDER.Value = ORDER.VALUE

AND

MIN2ORDER_FLAG = 0

END IF

Any ideas??

cheers chesterluck

[Gysbert_Wassenaar]

Try this:

subfield(ORDER,'+',1) as EDITED_ORDER

if(substringcount(ORDER,'+')>0,1,0) as MIN2ORDER_FLAG

[Anonymous]

While LOAD statement , right?

[Gysbert_Wassenaar · Accepted solution]

Yes, in the load statement where you retrieve the other fields from the same table.