Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hello! I am loading data from a csv file.
upper(if(isnull(CATEGORY),
if(0<index(upper(DESCRIPTION),'BARNARDT '),'AFTERCARE',
if(0<index(upper(DESCRIPTION),'HEAD TO TOE '),'BEAUTY',
if(0<index(upper(DESCRIPTION),'CREDIT AGREEMENT REP'),'CAR FINANCE',
if(0<index(upper(DESCRIPTION),'AUTOBANK'),'CASH',
if(0<index(upper(DESCRIPTION),'OTHER BANK ATM'),'CASH',
if(0<index(upper(DESCRIPTION),'CASH WITHDRAWAL FEE'),'CASH WITHDRAWAL FEE',
if(0<index(upper(DESCRIPTION),'CELL'),'CELL',
One of the colums contain string data, which can contain a load of junk eg....'MUTUAL fee 18493'. I want to somehow categorize all this data. So for this field, I would like to make it eg 'Mutual Fees'. So that all SIMILAR entries add up to MUTUAL fees.
I have tried doing it in IF statements (see code ) but after 70 IF's in the select part (YES!) it seems that it cannot handle more than that .
Is there an easier way to do this? Mapping tables are not going to do the trick since only PART of the string will match.
any help will do thankx
Mapping tables could help, check out this post on how to use a combination of mapsubstring() and textbetween() functions:
community.qlik.com/message/192097/
In the same thread, there is also the use of a regular expression search discussed, using a VBScript Macro.
Finally, you can also check out the mappingwithwildcards.qvw example from the QV cookbook:
http://robwunderlich.com/downloads/
Hope this helps,
Stefan