Create new dimensions by applying filter to current dimension

[#1]

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?

[ali_hijazi]

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)

[#3]

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

[ali_hijazi]

sorry I for late reply

I will get back to you in a while

currently I'm away from my development environment

[ali_hijazi]

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

[#6]

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