Data transformation in a QV front end of application

[Anonymous]

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):

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

What could be wrong with the transformation script?

[sunny_talwar · Accepted solution]

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

[Anonymous]

Many thanks Sunny, that works

[Anonymous]

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.