Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
M.
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 |
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
you can use a mapping approach with wildcards, e.g. as discussed here:
Maybe you could just use something like:
purgechar([Label operation], '0123456789') as Label
- Marcus
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
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?
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".
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 |
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!
Just replace the INLINE with your table source, e.g.
LOAD *,
TextBetween(MapsubString('CategoryMap',lower([Label Operation])),'\\','//') as Category
FROM YourExcelFile.xlsx (formatspecs);