Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

lakwinder
Contributor III

Nested If statement with Match

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Nested If statement with Match

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

15 Replies
MVP
MVP

Re: Nested If statement with Match

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?

lakwinder
Contributor III

Re: Nested If statement with Match

Sunny i've used AAAA etc for data protection.

MVP
MVP

Re: Nested If statement with Match

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?

lakwinder
Contributor III

Re: Nested If statement with Match

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'

lakwinder
Contributor III

Re: Nested If statement with Match

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'

MVP
MVP

Re: Nested If statement with Match

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

lakwinder
Contributor III

Re: Nested If statement with Match

I'm already using that syntax, can i not use the previous if statements with the match statements?

if(PRODUCT_FAMILY_DESC='2','BBBB',

MVP
MVP

Re: Nested If statement with Match

Is this what you want?

Old PRODUCT_FAMILY_DESCNew PRODUCT_FAMILY_DESC
1AAAA
2BBBB
3JJJJ
4JJJJ
5KKKK
6KKKK
7LLLL
8LLLL
9IIII
lakwinder
Contributor III

Re: Nested If statement with Match

This is what i require.

  

Old PRODUCT_FAMILY_DESCNew PRODUCT_FAMILY_DESC
1AAAA
2BBBB
3JJJJ
4JJJJ
5KKKK
6KKKK
7LLLL
8LLLL
9IIII
1,2,4,5GROUP 1
6,7GROUP 2
1,3,6,9GROUP 3