how to add a condition column in the loading script

[emilyrabbit]

HI, below is my origin data. 

there are different product in each order

in the loading script , I want to add a new column , if the product contain 'cat' ,and the order should be remark as 'cat order' . I wonder if I can use mapping load or left join. but I am not familiar with these 2 expression. Pls help me on it, thanks.

below is the target :

[Adam_Romanowski]

LOAD

[Order],

[Order line]

[Product],

if(Product='cat', 'cat order', 'not cat order') as [Order type]

from ...

[emilyrabbit]

Hi , the result is not correct. C contains "cat" , but order type still show "not cat order"

[emilyrabbit]

@MatheusC  can you also help me check it ?Thanks.

[Aditya_Chitale]

@emilyrabbit 

Maybe there are empty spaces in the column values. try using below expression:

if(wildmatch(trim(Product),'cat'), 'cat order', 'not cat order') as [Order type]

Regards,

Aditya

[emilyrabbit]

Hi， for some order line , there is not "cat" ,for instance ,C2 & C2 , but for the C order , it should show "cat order" . it isn't the match expression. 

[Aditya_Chitale · Accepted solution]

@emilyrabbit 

Are you looking for something like this ?

test:     //Load Base table
LOAD   
    "Order",
     Product
FROM [lib://TestFolder/Sales Test.xls]
(biff, embedded labels, table is Sheet1$);

left join (test)
//concat all products grouped on basis of order and 
//left join with base table
Load 
	"Order",
	concat(trim(Product),'|') as IsCat
Resident test 
group by "Order";

NoConcatenate

//use if condition to determine order type as a whole based on //order
final:   
load
	"Order",
     Product,
    if(wildmatch(IsCat,'*Cat*'),'Cat Order','not cat order') as "Order Type"
Resident test;

Drop table test;

Output:

Regards,

Aditya

[MatheusC]

I'm a little late.

Did you manage to solve it? It seems like @Aditya_Chitale  solution is what you're looking for, or is there something else I might be missing?

[emilyrabbit]

thanks . I found the solution ,using the mapping loading .