Inconsistent value format

[#1]

Hi,

I'm getting inconsistent result when I use Qlikview formatting in an IF condition.

I've attached the qvw and the excel file that I used.

I have the following columns in the attached excel file:

DESC --> just the field description

VALUE --> the value to be formatted

FORMAT --> the format type (e.g. DOUBLE, INT, PCT (percentage), STRING)

MASK --> the format to be used (e.g. '#,##0.00', '0.00%', etc...)

I loaded the VALUE field as text, because I need it to load exactly as in my source DB.

My problem is with the PCT (percent) format.

This expression will work: If(Upper(Trim(FORMAT))='PCT', Num(Num#(VALUE, '0.00%'),'0.00%'),VALUE) as PCT_FORMAT_ONLY

But this will not: If(Upper(Trim(FORMAT))='PCT', Num(Num#(VALUE, MASK),MASK),VALUE) as PCT_FORMAT_ONLY2

And if I added an IF condition for other format types, then the PCT format will work on some rows, but not on other rows.

If I use this expression:

If(Upper(Trim(FORMAT))='PCT', Num(Num#(VALUE, '0.00%'),'0.00%'),

                    If(Upper(Trim(FORMAT))='DOUBLE',Num(Num#(VALUE, MASK), MASK),VALUE)) as VALUE_FORMAT2

Some rows will correctly be formatted to percentage, but on some rows the formatting won't be apply.

I noticed, the format will not be apply if the value is 1.0000 or 0.

Please see the VALUE_FORMAT2 column in the attached qvw file.

Appreciate all your help.

Thanks!

[Gysbert_Wassenaar]

Your 0% mask in excel is just a zero with formatting. When loading the data from excel it only loads a 0. Formatting is not loaded. If you change the value in excel to a string then it will load as a string and can be used in the num and num# functions as a format string.

[#3]

Hi Gysbert,

Even if I don't use the mask, and explicitly put in the percentage format, its still doesn't work.

This is the expression in my script that doesn't work:

If(Upper(Trim(FORMAT))='PCT', Num(Num#(VALUE, '0.00%'),'0.00%'),

                    If(Upper(Trim(FORMAT))='DOUBLE',Num(Num#(VALUE, MASK), MASK),VALUE)) as VALUE_FORMAT2

I didn't use the 0% mask that was loaded from excel. I put in the actual percentage format, but it doesn't work.

[Gysbert_Wassenaar · Accepted solution]

Oh, that. Yeah, that's a bit weird. You might call it a bug. Try adding a small fraction the VALUE for the PCT values:

If(Upper(Trim(FORMAT))='PCT', Num(0.00000000001+Num#(VALUE, MASK), MASK),If(Upper(Trim(FORMAT='DOUBLE')),Num(Num#(VALUE, MASK), MASK), VALUE)) as VALUE_FORMAT3,

[#5]

thank you. that trick works! 🙂

thanks again.