Alternative to Nested IF - too complex

[sstefancies]

I have been using nested IF statements saved in a master dimension to keep track of large groups of big customers.  Below is a VERY brief version of what I’m using:

=IF( left([c2.name],7) = 'BLOOMIN' ,  'Bloomin Brands',

IF( left([c2.name],7) = 'OUTBACK' ,  'Bloomin Brands',

IF( left([c2.name],8) = 'CARRABBA' ,  'Bloomin Brands',

IF( left([c2.name],9) = 'FLEMING''S' ,  'Bloomin Brands',

IF( left([c2.name],8) = 'FLEMINGS' ,  'Bloomin Brands',

IF( left([c2.name],8) = 'BONEFISH' ,  'Bloomin Brands',

IF( left([c2.Address],6) = 'COSTCO'  ,   'Costco/Hardt',

IF( left([c2.Address],6) = 'P.F.CH'  ,   'PEI WEI',

IF( left([c2.Address],7) = 'PEI WEI'  ,   'PEI WEI',

'Other' )))))))))

This works really well to show large groups of customers that all have different names and it gives me the ability to capture several different variations of customer names based on how users input the data.  In a pivot table, the above example would have one line for “Bloomin Brands” and include everything that you see listed.  It’s actually rolled up even further to group them into which third party they use so the pivot table looks like this:

I am now running into a problem where the expression is starting to error out because it is too long.  What is an alternative way of accomplishing this?

[Vegar]

I'm a frequent  user of @rwunderlich QlikView Components. In the QVC library you will find the QVC.wildmap, it should be able to solve your problem.

Regardless of the name QVC is very useful in Qlik Sense as well as QlikView. 

Check out the library here: https://github.com/RobWunderlich/Qlikview-Components

[gmenoutis]

The obvious solution would be to create a dedicated field for this instead of a (calculated!) dimension. It is unlikely the script will apply a space limitation to a formula.

[sstefancies]

Are you suggesting that I use a calculated field?

I'm working through this solution but am running into problems.  I tried to add a calculated field in the Data Manager (which I haven't used much) but it doesn't seem to allow IF statements, or at least not multiple IF statements.  It's also very difficult to use since you can't expand the editor box and navigating through large scripts is incredibly painful.  

I have moved on to trying to add a calculated field in the load script but am having trouble locating the correct syntax as it pertains to IF statements.  I found the simple answer (LOAD Field1 + Field2 AS Field3), but this isn't helpful in this scenario.  

Before I spend more time going down this rabbit hole, is this even what you meant? To add a calculated field?

[neelamsaroha157]

You can try this - 

=IF( wildMatch([c2.name], 'BLOOMIN' , 'OUTBACK' , 'CARRABBA, 'FLEMING''S' , 'FLEMINGS' ,'BONEFISH' ), 'Bloomin Brands',

IF( wildMatch([c2.Address] , 'COSTCO' ) ,   'Costco/Hardt',

IF( wildMatch([c2.Address],  'P.F.CH','PEI WEI')  ,   'PEI WEI', 'Other')))

(Please check brackets & commas)

[sstefancies]

Using wildMatch almost worked.  I'll have to dig into wildMatch some more to see what I'm doing wrong.  Some lines work, while most don't.  The following:

IF( wildMatch([c2.name], 'CAVA') , 'Cava',
IF( wildMatch([c2.name], 'RA SUSHI','BENIHANA') , 'Benihana',
IF( wildMatch([c2.name], 'P.F.CH','PEI WEI') , 'PEI WEI',
IF( wildMatch([c2.name], 'Aarsand') , 'Aarsand',
IF( wildMatch([c2.name], 'BLOOMIN','OUTBACK','CARRABBA','FLEMING','BONEFISH') , 'Bloomin',
IF( wildMatch([c2.name], 'CHICK-','CHICK ') , 'Chick-Fil-A',

'Other'))))))

Returned:

The data alone doesn't explain this since the Bloomin group has a lot of data in it, while the PEI WEI group has very little.  

In the mean time, using a calculated field in the load script seems to be working.  The way apostrophes are handled in expressions is not the same as the load script so I had to use ascii.  But for now, this seems to be the best solution.

Thank you to everyone who contributed.

[Vegar · Accepted solution]

It looks like you are missing  wildchars in your expression.

IF( wildMatch([c2.name], 'CAVA') , 
    'Cava',
    IF( wildMatch([c2.name], 'RA SUSHI','BENIHANA') , 
        'Benihana',
        IF( wildMatch([c2.name], 'P.F.CH*','PEI WEI*') , 
            'PEI WEI',
            IF( wildMatch([c2.name], 'Aarsand*') , 
                'Aarsand',
                IF( wildMatch([c2.name], 'BLOOMIN*', 'OUTBACK*', 'CARRABBA*', 'FLEMING*', 'BONEFISH*') , 
                    'Bloomin',
                    IF( wildMatch([c2.name], 'CHICK-*','CHICK *') , 
                        'Chick-Fil-A',
                        'Other')
                     )
                )
             )
         ) 
     )

 

[sstefancies]

Thank you.  That helps.  This is my first time using wildMatch(), so thank you for the clarity.

[gmenoutis · Accepted solution]

First, for clarity, let me note that there is no such thing as "calculated filed", but there is a thing called "calculated dimension". And surely enough, I am not suggesting the use of this.

But it seems you use "calculated field" in order to describe a data model field created via the script during reload. Yes, that is what I am suggesting.

It really shouldn't be difficult, and the if statements are not different than what you use in an expression. Here is a small guide:

1) Open the data model viewer and write down the name of the table that contains the fields [c2.name],[c2.Address]. Hopefully it's the same table- if it's not, you'll have to use more complicated join logic. In that case, stop reading this and just post the table names. Continuing with the assumption that both fields are in a table called [tablename],

2) go to the end of the script (or any place after the last command that modifies the content of [tablename]) and add the following script:

rename table [tablename] to [tablename_temp];

tablename:

NOCONCATENATE LOAD *,

YOUR_VERY_LARGE_IF_EXPRESSION_HERE_EXACTLY_AS_IS as [ServiceChannel]

resident [tablename_temp];

drop table [tablename_temp];

What this script does is rename the original table to _temp, and create a new table with the original name which includes everything the old had PLUS your new ServiceChannel field.

After that, you will be able to see the field on your app.

[sstefancies]

I love this!  This solution actually eliminates the need for me to do the entire script twice.  ServiceChannel is one of about ten different third parties that each of these customer groups rolls up to.  Originally, I had two very long nested IF statements, one to place each into a group and another to further place those groups into their third party categories.  

This solution allows me to add and remove customers from the model only once as our customer base grows and changes.  

Thank you!