Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
chitemerere
Specialist
Specialist

Data transformation in a QV front end of application

I am trying to transform data in the front end of a QV application instead of changing the original data base from my MS SQL server as follows (the field 'CATEFORY' is being transformed):

Screen Shot 2016-02-22 at 23.33.44.png

The script is on one line.  However, i am getting the untransformed original data in my report (under the Dimension MCAZCategory) as follows:

Screen Shot 2016-02-22 at 23.25.10.png

What could be wrong with the transformation script?

1 Solution

Accepted Solutions
sunny_talwar

Can you try this:

If(Match(Trim(CATEGORY), 'HRA', 'HR', 'PID', 'PIM', 'P', 'PR'), 'OTC',

If(Match(Trim(CATEGORY), 'VMGD', 'PP', 'PP10', 'N'), 'PRESCRIPTION', CATEGORY)) AS MCAZCategory


UPDATE: Missed a ending parenthesis

View solution in original post

4 Replies
sunny_talwar

Can you try this:

If(Match(Trim(CATEGORY), 'HRA', 'HR', 'PID', 'PIM', 'P', 'PR'), 'OTC',

If(Match(Trim(CATEGORY), 'VMGD', 'PP', 'PP10', 'N'), 'PRESCRIPTION', CATEGORY)) AS MCAZCategory


UPDATE: Missed a ending parenthesis

Oleg_Troyansky

The problem is caused by the wrong position of the comma in your nested IF statement. For example:

IF(Category='ABC', 'X'), IF(Category='DEF', 'Y'), ... as MyField

This statement should produce a lot of fields that you never asked for, because each IF is considered a separate field. Instead, the comma that means ELSE, should be positioned before the closing parenthesis:

IF(Category='ABC', 'X', IF(Category='DEF', 'Y', ...  )) as MyField

Notice the parts of the script that I underlined.

Having said that, I'd like to mention that this is a very inefficient way of managing your categorization. Instead, I'd recommend loading a MAPPING table of Categories and MCAZCategories and using the ApplyMap() function to map categories accordingly. You will avoid so many misspelling errors and simplify maintenance by a lot!

Check out my new book QlikView Your Business to learn about many advanced QlikView techniques.

cheers,

Oleg Troyansky

chitemerere
Specialist
Specialist
Author

Many thanks Sunny, that works

chitemerere
Specialist
Specialist
Author

Many thanks Oleg.  Is the book available in pdf format and from which seller?  I have seen Amazon which does not include the pdf format.

Regards.