Category

[tan_chungkam]

Hi all,

I would like to do a scripting (preferred) or a set analysis to create the 'Term' column as shown below.

My question is

if one of the MainID has 'Final' or 'Normal' in their Type then their Term should be  P/L,

If one of the MainID doesn't have 'Final' or 'Normal' in their Type then their Term is B/S.

MainID	SubID	Type	Term
1	A	partial	p/l
1	B	partial	p/l
1	C	partial	p/l
1	D	final	p/l
2	A	partial	b/s
2	B	partial	b/s
2	C	partial	b/s
2	D	partial	b/s
3	A	normal	p/l
3	B	partial	p/l
4	A	normal	p/l
5	A	final	p/l
5	B	partial	p/l
6	A	final	p/l

[tresesco · Accepted solution]

Try like:

t1:
Load  * From <>;
Join
Load 
    MainID,	
    if(WildMatch(Concat(Distinct Type),'*final*','*normal*'),'p/l','b/s') as Term
Resident t1 Group By MainID;	

[thannila]

Data:
load * inline
[
MainID,SubID,Type,Term
1,A ,partial,p/l
1,B,partial,p/l
1,C,partial,p/l
1,D,final,p/l
2,A,partial,b/s
2,B,partial,b/s
2,C,partial,b/s
2,D,partial,b/s
3,A,normal,p/l
3,B,partial,p/l
4,A,normal,p/l
5,A,final,p/l
5,B,partial,p/l
6,A,final,p/l
];

NoConcatenate
Data1:
load * resident Data
where Type='final' or Type='normal' and Term='p/l';
Concatenate
load * resident Data
where Type='partial'and Term='b/s';
drop table Data;

[tan_chungkam]

Hi Tresesco,

Thanks for the help. 

It works perfectly at the moment.

I would really appreciate if you could explain on your scripting. 

[tresesco]

Using aggregation function conact(), getting all possible Types against individual MainIDs, and then using wildmatch() checking if there is any of 'final' or 'normal' ....

Hope this helps.

Please note, in this new community version - one important way of appreciation (of contribution by helpers) is to 'Like' his/her post. Hence. please don't forget to 'Like' post alongside accepting it as a 'solution'.

[tan_chungkam]

Thanks buddy.
Hope you had a good Christmas 🙂