Nested If statement with Match

[Anonymous]

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);

[sunny_talwar]

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?

[Anonymous]

Option 2

[sunny_talwar · Accepted solution]

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

[Anonymous]

I'll give it a go.

Thanks

[Anonymous]

Cheers mate that worked a treat.

[sunny_talwar]

Awesome