Discussion board where members can get started with Qlik Sense.
I got my dataset as below:
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;
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?
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
suppose your original table is called T1 then your script should be something like this
load *, if([Product Family] = 'WW', OPP_NUM) as WW_OPP_NUM
,if([Product Family] <> 'WW' ,OPP_NUM) as NON_WW_OPP_NUM
drop table T1;
if( isnull(ApplyMap('mappingtable1',OPP_NUM,Null())),'N','Y') AS Cross_Sell
drop table T2;
hope this helps
NOTE: IOT_OPP_NUM doesn't exist and I replaced it with OPP_NUM (I assumed)
!@@ ! !
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:
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.
sorry I for late reply
I will get back to you in a while
currently I'm away from my development environment
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
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?