Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I got my dataset as below:
Opp_Num | OPP_Line_Item | Product Family | Cross Sell |
111111 | 1A | WW | Y |
111111 | 1B | AA | Y |
222222 | 2A | WW | N |
222222 | 2B | WW | N |
222222 | 2C | AA | N |
333333 | 3A | BB | Y |
333333 | 3B | WW | Y |
333333 | 3C | AA | Y |
444444 | 4A | BB | N |
555555 | 5A | AA | N |
666666 | 6A | BB | N |
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?
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)
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:
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
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
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