IF & AND function in QLIK SENSE

[Aure35]

Good morning,

I would like to build report to get data in each dealer network we work with, based on the tractor brands that they retail and considering that a dealer can retail several brands of tractors.

Exemple: A company retails 3 brands of tractors: ANTONIO CARRARO, CASE and NEW HOLLAND.

I want to create a dimension named "DEALER NETWORK" that filters easily all dealers who retail at least each brand of tractor. If the TRACTOR_BRAND_DELIVERED field holds "NEW HOLLAND", the company is identified as a "NEW HOLLAND" dealer; and if TRACTOR_BRAND_DELIVERED field holds "CASE", the company is identified as a "CASE" dealer.

I have tried 2 frond-end expressions but only the first condition gets applied as a result:

1st one:

=if (TRACTOR_BRAND_DELIVERED like '*case*', 'CASE/STEYR',
if (TRACTOR_BRAND_MAIN like '*case*' , 'CASE/STEYR',

if (TRACTOR_BRAND_DELIVERED like '*HOLLAND*', 'NEW HOLLAND',
if (TRACTOR_BRAND_MAIN like '*HOLLAND*' , 'NEW HOLLAND',

'OTHER NETWORKS'))))

2nd one;

=if (WildMatch(TRACTOR_BRAND_DELIVERED,'*case ih*'), 'CASE/STEYR',
if (WildMatch(TRACTOR_BRAND_MAIN,'*case ih*'), 'CASE/STEYR',

if (WildMatch(TRACTOR_BRAND_DELIVERED, '*HOLLAND*'), 'NEW HOLLAND',
if (WildMatch(TRACTOR_BRAND_MAIN,'*HOLLAND*'), 'NEW HOLLAND',


'OTHER NETWORKS'
))))

I have tried to add a "AND" but I don't know how to use it.

Could you please help me?

Thanks!

Aurélie

[Chanty4u]

Try this 

1 st expression

=if(WildMatch(TRACTOR_BRAND_DELIVERED, '*case*') OR WildMatch(TRACTOR_BRAND_MAIN, '*case*'), 'CASE/STEYR',

if(WildMatch(TRACTOR_BRAND_DELIVERED, '*HOLLAND*') OR WildMatch(TRACTOR_BRAND_MAIN, '*HOLLAND*'), 'NEW HOLLAND',

'OTHER NETWORKS'

))

2nd expression:

=if(WildMatch(TRACTOR_BRAND_DELIVERED, '*case ih*') OR WildMatch(TRACTOR_BRAND_MAIN, '*case ih*'), 'CASE/STEYR',

if(WildMatch(TRACTOR_BRAND_DELIVERED, '*HOLLAND*') OR WildMatch(TRACTOR_BRAND_MAIN, '*HOLLAND*'), 'NEW HOLLAND',

'OTHER NETWORKS'

))

[Aure35]

Hello,

No, unfortunately, it does not work. With any of my 2 and your 2 solutions, I get this result:

As the first conditions is fullfilled, it ignores the other 2 conditions.

Yet, as there are 3 brands, the lines should be tripled. See the result that I expect:

Do you have any ideas?

Thanks,

Aurélie

[stevedark]

Hi @Aure35 

I would strongly suggest dealing with this in the load script, but similar can be used in a calculated dimension (it will just be less efficient and selections will be weird).

The nested IF syntax means that you don't need an and, it should try each condition in turn. If it is never picking up New Holland I would suggest that the data may not be as you expect.

Personally I would tend to use Index to find partial matches, so in the load script I would have:

LOAD
    TRACTOR_BRAND_DELIVERED,
    TRACTOR_BRAND_MAIN,
     if (index(upper(TRACTOR_BRAND_DELIVERED), 'CASE') > 0, 'CASE/STEYR',
        if (index(upper(TRACTOR_BRAND_MAIN), 'CASE') > 0, 'CASE/STEYR',
        if (index(upper(TRACTOR_BRAND_DELIVERED), 'HOLLAND') > 0, 'NEW HOLLAND',
        if (index(upper(TRACTOR_BRAND_MAIN), 'HOLLAND') > 0, 'NEW HOLLAND',
       'OTHER NETWORKS')))) as [DEALER NETWORK],

You can then just use DEALER NETWORK as a dimension.

To check what is going on create a simple table with just TRACTOR_BRAND_DELIVERED, TRACTOR_BRAND_MAIN and DEALER NETWORK as dimensions to see whether it is working as expected. If it is not please post a screenshot of that table to show where it is not working.

If the issue is that you want to show both networks if both CASE and HOLLAND are found in the text you need to use & to stitch two bits of text together:

LOAD
    TRACTOR_BRAND_DELIVERED,
    TRACTOR_BRAND_MAIN,
     trim(if (index(upper(TRACTOR_BRAND_DELIVERED), 'CASE') > 0, 'CASE/STEYR',
        if (index(upper(TRACTOR_BRAND_MAIN), 'CASE') > 0, 'CASE/STEYR', '')) & 
        if (index(upper(TRACTOR_BRAND_DELIVERED), 'HOLLAND') > 0, ' NEW HOLLAND',
        if (index(upper(TRACTOR_BRAND_MAIN), 'HOLLAND') > 0, ' NEW HOLLAND', '')) &
        if(index(upper(TRACTOR_BRAND_MAIN), 'CASE') +
             index(upper(TRACTOR_BRAND_DELIVERED), 'CASE') +
             index(upper(TRACTOR_BRAND_MAIN), 'HOLLAND') +
             index(upper(TRACTOR_BRAND_DELIVERED), 'HOLLAND') = 0,
                 'OTHER NETWORKS', '') as [DEALER NETWORK],

It's a bit messy, as you have to decide whether you are showing CASE/STEYR first, then appending NEW HOLLAND if that is found and only having the text OTHER NETWORKS if neither of the other cases is true.

Note the difference between & which is concatenating text and + which is adding numbers together.

Hope that helps,

Steve

https://www.quickintelligence.co.uk/blog/