topic Re: Pattern of nested if in QlikView

Pattern of nested if

Thu, 08 Dec 2016 07:30:50 GMT

Hello all,

I realized Qlikview is limited to a 100 nested if. What I am trying to do is "relabel" the labels I have for different transactions.

For example: "15072016 77635536 MCDONALDS UUTZR" I want to have another column "Subcategory" with mure "cleaner labels" in which it would be written "McDonalds" for this one.

The nested if method is working fine:

if( index([Label operation], 'TAXI')<> 0 ,'Taxi', // Here Label operation is : 425252 TAXIG763355

if( index([Label operation], 'BURGERKING')<> 0 ,'Burger King', // Here Label operation is :16072016 BURGERKING37R38

'Others' AS Subcategory

But I have many label to rename more than 100 and thus , if nested does not work anymore.

I was trying to investigate ApplyMap, but note sure how I would use it. It should be something not sor far to this:

ApplyMap('map1', [Label operation],'Others') As Subcategory

But I guess I have to still use index function.

Let me know if you come up with a nice idea

Thanks.

Re: Pattern of nested if

datanibbler — Thu, 08 Dec 2016 07:38:11 GMT

Hi,

one alternative to nested IFs - far less complicated and far more manageable - is PICK(MATCH()) - you have one line per possibility and every line with an opening and closing bracket, not so many brackets at the very end ...

But for >100 possibilities, I guess you will need something better.

Applymap() would be a possibility if each of the IDs you have is unique.

I think there is also something like a VLOOKUP in QlikView though I haven't used it yet.

Maybe one of the Gurus here can tell you what would be best in terms of performance - I would go for either Applymap or - probably the most manageable and the most easily understandable - PICK(MATCH().

Best regards,

DataNibbler

Re: Pattern of nested if

swuehl — Thu, 08 Dec 2016 07:40:37 GMT

you can use a mapping approach with wildcards, e.g. as discussed here:

Apply map replacing IF | Qlik Community‌

Recipes | Qlikview Cookbook

Re: Pattern of nested if

marcus_sommer — Thu, 08 Dec 2016 07:43:14 GMT

Maybe you could just use something like:

purgechar([Label operation], '0123456789') as Label

- Marcus

Re: Pattern of nested if

Thu, 08 Dec 2016 12:07:14 GMT

Thank you for the great links Stepfan. (even though I am not sure how to get the samples in QV Cookbook)

But I tried the method in the qvw in the first link with the pick and wildmatch.

Where I am not sure is how to fill the wildmap table in my case, like this ?:

wildmap:

LOAD

* INLINE [

Key, Label

?MCDONALDS*, McDonalds

?TAXI*, Taxi

*, Other

]

;

And for the last table in step 3 I see Customer, PartNo and PartGroup but I'm not sure what they would correspond in my case.

Because I am looking for a kind of automated way, where I would not need to copy the content of [Label operation] in the script

Re: Pattern of nested if

swuehl — Thu, 08 Dec 2016 12:46:46 GMT

Could you post a sample table (Excel file) with your Label field values (sample records), a second table with the search string within the label text and the subcategory text you want to assign if the search string matches within label?

Re: Pattern of nested if

Thu, 08 Dec 2016 14:01:49 GMT

Hey Stefan,

So let me post a sample of my data

Date	Label Operation	Amount
14/11/2016	111116 BURGER KING LTD CARTE 2342XXXX	-9,19
20/09/2016	190916 COCO DI MAMA 21.09	-14,04
20/06/2016	170616 MCDONALD S 7,90	-7,9
05/09/2016	010916 MCDONALDS CARTE 2342XXXXXXXX55	-10,46

From this, I would like to create categories, so for example, group "MCDONALDS" and "MCDONALD S" under "McDonalds" in this new column "Category"

So this is why I would like to automate the reading of this "Label Operation" field. That might be possible with the methodology you gave me but I am not clear on how to make it work with the column "Label Operation".

Re: Pattern of nested if

swuehl — Fri, 09 Dec 2016 08:27:44 GMT

Maybe like this (you just need to find search strings that matches your label operation values per category, could also be only fractions of the complete category, like 'mcd' for McDonalds and you can also use multiple lines / search strings per category) :

CategoryMap:

MAPPING

LOAD Search, '\\'&Category&'//' INLINE [

Search, Category

burger king, Burger King

mcdonald, McDonalds

coco, Coco Di Mama

];

LOAD *,

TextBetween(MapsubString('CategoryMap',lower([Label Operation])),'\\','//') as Category

INLINE [

Date, Label Operation, Amount

14/11/2016, 111116 BURGER KING LTD CARTE 2342XXXX, "-9,19"

20/09/2016, 190916 COCO DI MAMA 21.09, "-14,04"

20/06/2016, 170616 MCDONALD S 7,90, "-7,9"

05/09/2016, 010916 MCDONALDS CARTE 2342XXXXXXXX55, "-10,46"

];

Label Operation	Category	Amount	Date
010916 MCDONALDS CARTE 2342XXXXXXXX55	McDonalds	-10,46	05/09/2016
111116 BURGER KING LTD CARTE 2342XXXX	Burger King	-9,19	14/11/2016
170616 MCDONALD S 7	McDonalds	90	20/06/2016
190916 COCO DI MAMA 21.09	Coco Di Mama	-14,04	20/09/2016

Re: Pattern of nested if

Fri, 09 Dec 2016 10:09:28 GMT

Hi Stefan,

A really big thanks for the time you took for this. It seems like the perfect approach!

One last thing I would like to clarify and feel a bit shameful...

The last part where you LOAD Inline with all the data, I would obviously need to replace that Inline by the original table I have which is looking something like this:

Label Operation	Amount	Date
010916 MCDONALDS CARTE 2342XXXXXXXX55	-10,46	05/09/2016
111116 BURGER KING LTD CARTE 2342XXXX	-9,19	14/11/2016
170616 MCDONALD S 7	90	20/06/2016
190916 COCO DI MAMA 21.09	-14,04	20/09/2016

But my real original table has more than 500 transactions so I obviously cannot copy/paste the data in the script.

So to be clearer, I am missing how to write the part with the question mark below:

Sorry if the answer is obvious!

Re: Pattern of nested if

swuehl — Fri, 09 Dec 2016 10:33:16 GMT

Just replace the INLINE with your table source, e.g.

LOAD *,

TextBetween(MapsubString('CategoryMap',lower([Label Operation])),'\\','//') as Category

FROM YourExcelFile.xlsx (formatspecs);

Re: Pattern of nested if

Fri, 09 Dec 2016 10:59:40 GMT

Crystal clear and working perfect

Again big thanks Stefan, this helps me a lot !