topic Re: Data cleansing - Create a new column in QlikView

Data cleansing - Create a new column

Anonymous — Wed, 07 Nov 2012 15:07:15 GMT

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 — Wed, 07 Nov 2012 15:10:54 GMT

Try this:

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

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

Anonymous — Wed, 07 Nov 2012 15:18:01 GMT

While LOAD statement , right?

Gysbert_Wassenaar — Wed, 07 Nov 2012 15:24:44 GMT

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