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);
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
This is because the allocation of if would have already been made above. For instance if PRODUCT_FAMILY_DESC = 'AAAA', it will be assigned 'AAAA' and will never go down to -> if(MATCH(PRODUCT_FAMILY_DESC,'AAAA','BBBB'),'JJJJ', and will never be assigned JJJJ.
This is true for all others too.
What exactly are you trying to do? May be what you are doing isn't the way to do it. Can you elaborate your requirement?
Sunny i've used AAAA etc for data protection.
I understand that, but what I am trying say that each row can only be assigned a single value. If it's already assigned AAAA, how can it be assigned JJJJ for the same row?
I'm trying to do,
If the rows in the field are 1, 2, 3, 4 and 5, i want to call them grouped together as say 'group 1'
I get what you mean now, i've explained myself a little better below.
I'm trying to do this,
If the rows in the field are 1, 2, 3, 4 and 5, i want to call them grouped together as say 'group 1'
Then may be something like this:
If(Match(FieldName, 1, 2, 3, 4, 5), 'Group 1'),
If(Match(FieldName, 6, 7, 8, 9, 10), 'Group 2'),
If(Match(FieldName, 11, 12, 13, 14, 15), 'Group 3'))) as NewFieldName
I'm already using that syntax, can i not use the previous if statements with the match statements?
if(PRODUCT_FAMILY_DESC='2','BBBB',
Is this what you want?
Old PRODUCT_FAMILY_DESC | New PRODUCT_FAMILY_DESC |
---|---|
1 | AAAA |
2 | BBBB |
3 | JJJJ |
4 | JJJJ |
5 | KKKK |
6 | KKKK |
7 | LLLL |
8 | LLLL |
9 | IIII |
This is what i require.
Old PRODUCT_FAMILY_DESC | New PRODUCT_FAMILY_DESC |
1 | AAAA |
2 | BBBB |
3 | JJJJ |
4 | JJJJ |
5 | KKKK |
6 | KKKK |
7 | LLLL |
8 | LLLL |
9 | IIII |
1,2,4,5 | GROUP 1 |
6,7 | GROUP 2 |
1,3,6,9 | GROUP 3 |