Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
Why doesn't the following If statement show me the last three fields that i created? 'JJJJ', 'KKKK' and 'LLLL' (The Match syntax ones), i can see from AAAA to IIII.
Thanks
[PRODUCT]:
Mapping LOAD
SID_PRODUCT,
if(PRODUCT_FAMILY_DESC='1','AAAA',
if(PRODUCT_FAMILY_DESC='2','BBBB',
if(PRODUCT_FAMILY_DESC='3','CCCC',
if(PRODUCT_FAMILY_DESC='4','DDDD',
if(PRODUCT_FAMILY_DESC='5','EEEE',
if(PRODUCT_FAMILY_DESC='6','FFFF',
if(PRODUCT_FAMILY_DESC='7','GGGG',
if(PRODUCT_FAMILY_DESC='8','HHHH',
if (PRODUCT_FAMILY_DESC='9','IIII',
if(MATCH(PRODUCT_FAMILY_DESC,'3','4'),'JJJJ',
If(match(PRODUCT_FAMILY_DESC,’4','5'),'KKKK',
IF(MATCH(PRODUCT_FAMILY_DESC,'6','7'),'LLLL')))))))))))) as PRODUCT_FAMILY_DESC
FROM [lib://QVD_FOLDER (XXXXXXX)/PRODUCT.qvd]
(qvd);
So lets say you have this row of data:
Dim1, Dim2, Dim3, PRODUCT_FAMILY_DESC
P, PP, PPP, 1
This should give you
1)
Dim1, Dim2, Dim3, PRODUCT_FAMILY_DESC, Group
P, PP, PPP, AAAA, Group 1
or
2)
Dim1, Dim2, Dim3, PRODUCT_FAMILY_DESC
P, PP, PPP, AAAA
P, PP, PPP, Group 1
Are you looking for option 1 or option 2 as output?
Option 2
Then you need something like this:
TempPRODUCT:
LOAD SID_PRODUCT,
PRODUCT_FAMILY_DESC,
Pick(Match(PRODUCT_FAMILY_DESC, 1, 2, 3, 4, 5, 6, 7, 8, 9), 'AAAA', 'BBBB', 'CCCC', 'DDDD', 'EEEE', 'FFFF', 'GGGG', 'HHHH', 'IIII') as NEW_PRODUCT_FAMILY_DESC
FROM QVD;
Concatenate(TempPRODUCT)
LOAD SID_PRODUCT,
PRODUCT_FAMILY_DESC,
'Group 1' as NEW_PRODUCT_FAMILY_DESC
Resident TempPRODUCT
Where Match(PRODUCT_FAMILY_DESC, 1, 2, 4, 5);
Concatenate(TempPRODUCT)
LOAD SID_PRODUCT,
PRODUCT_FAMILY_DESC,
'Group 2' as NEW_PRODUCT_FAMILY_DESC
Resident TempPRODUCT
Where Match(PRODUCT_FAMILY_DESC, 6, 7);
Concatenate(TempPRODUCT)
LOAD SID_PRODUCT,
PRODUCT_FAMILY_DESC,
'Group 3' as NEW_PRODUCT_FAMILY_DESC
Resident TempPRODUCT
Where Match(PRODUCT_FAMILY_DESC, 1, 3, 6, 9);
But this need to be joined to your fact table instead of a mapping load because mapping load will skip the duplicate NEW_PRODUCT_FAMILY_DESC per SID_PRODUCT.
HTH
Best,
Sunny
I'll give it a go.
Thanks
Cheers mate that worked a treat.
Awesome