Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Create new dimensions by applying filter to current dimension

Hi All,

I got my dataset as below:

   

Opp_NumOPP_Line_ItemProduct FamilyCross Sell
1111111AWWY
1111111BAAY
2222222AWWN
2222222BWWN
2222222CAAN
3333333ABBY
3333333BWWY
3333333CAAY
4444444ABBN
5555555AAAN
6666666ABBN

The first 3 columns are the existing column in my data model.

The last column (cross sell) is the column that should be calculated according to logic as below:

if Opp_Num contains OPP_line_items with WW and also OTHER product family such as AA,BB will be considered as 'Y'...

I tried to use mapping load and apply map as below:

IF(Family="WW",OPP_NUM) as WW_OPP_NUM;

IF(Family-="WW",OPP_NUM) as NON_WW_OPP_NUM;

Mappingtable1:

Mapping load

OPP_NUM,

NON_WW_OPP_NUM

if( isnull(ApplyMap('mappingtable1',IOT_OPP_NUM,Null())),'N','Y') AS Cross_Sell

However, the code cannot be loaded successfully.

Anyone can help on this?

5 Replies
ali_hijazi
Honored Contributor

Re: Create new dimensions by applying filter to current dimension

first of all if you need to use mapping table then the first column should have unique values

in your case OPP_NUM contains 222222 in more than one row but the applymap will return always the first value

Anyways

suppose your original table is called T1 then your script should be something like this


T2:

load *, if([Product Family] = 'WW', OPP_NUM) as WW_OPP_NUM

,if([Product Family] <> 'WW' ,OPP_NUM) as NON_WW_OPP_NUM

resident T1;

drop table T1;

mappingtable1:

Mapping Load

OPP_NUM,

NON_WW_OPP_NUM

resident T2;

t3:

load *,

if( isnull(ApplyMap('mappingtable1',OPP_NUM,Null())),'N','Y') AS Cross_Sell

resident T2;

drop table T2;


hope this helps

NOTE: IOT_OPP_NUM doesn't exist and I replaced it with OPP_NUM (I assumed)



Not applicable

Re: Create new dimensions by applying filter to current dimension

Hi Ali,

!@@ ! !

Thanks for your help.

Your advise really helped me to move a big step.

However, there is a little condition that still cannot be fulfilled like below:

IOT_Cross_Sell.png

Those highlighted in red should return 'Y' in Cross_Sell instead of 'N'.

Been struggled for quite a moment but i still cannot get the final result to return as 'Y'.

I attached the qvf.file here.

Hope to get some advise.

Thanks.

Shareen


ali_hijazi
Honored Contributor

Re: Create new dimensions by applying filter to current dimension

sorry I for late reply

I will get back to you in a while

currently I'm away from my development environment

ali_hijazi
Honored Contributor

Re: Create new dimensions by applying filter to current dimension

that's because family is Wireless i.e. WW then for sure the NON_WW_OPP_NUM will be null() because NON_WW_OPP_NUM will get value if the family is not WW

Not applicable

Re: Create new dimensions by applying filter to current dimension

Hi Ali,

sorry for my late reply.

Being trying out all the alternative I can but i still cannot get the required result.

The problem is I need those WIRELESS that I highlighted to be Y.

Will you have any better way to get this result?

regards,

Shareen

Community Browser