Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
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;
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;
Hi Tresesco,
Thanks for the help.
It works perfectly at the moment.
I would really appreciate if you could explain on your scripting.
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'.