Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
tan_chungkam
Contributor

Category

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.

 

MainIDSubIDTypeTerm
1Apartialp/l
1Bpartialp/l
1Cpartialp/l
1Dfinalp/l
2Apartialb/s
2Bpartialb/s
2Cpartialb/s
2Dpartialb/s
3Anormalp/l
3Bpartialp/l
4Anormalp/l
5Afinalp/l
5Bpartialp/l
6Afinalp/l
Labels (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Category

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;	
5 Replies
MVP
MVP

Re: Category

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
Contributor

Re: Category

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
Contributor

Re: Category

Hi Tresesco,

Thanks for the help. 

It works perfectly at the moment.

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

MVP
MVP

Re: Category

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
Contributor

Re: Category

Thanks buddy.
Hope you had a good Christmas 🙂