Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

loading text from .csv file

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

Tags (1)
1 Reply
Highlighted
MVP
MVP

loading text from .csv file

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

Community Browser