Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
sunny_talwar

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

View solution in original post

15 Replies
sunny_talwar

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?

Anonymous
Not applicable
Author

Sunny i've used AAAA etc for data protection.

sunny_talwar

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?

Anonymous
Not applicable
Author

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'

Anonymous
Not applicable
Author

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'

sunny_talwar

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

Anonymous
Not applicable
Author

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

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

sunny_talwar

Is this what you want?

Old PRODUCT_FAMILY_DESCNew PRODUCT_FAMILY_DESC
1AAAA
2BBBB
3JJJJ
4JJJJ
5KKKK
6KKKK
7LLLL
8LLLL
9IIII
Anonymous
Not applicable
Author

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