Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Inconsistent value format

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!

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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,


talk is cheap, supply exceeds demand

View solution in original post

4 Replies
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.


talk is cheap, supply exceeds demand
Not applicable
Author

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

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,


talk is cheap, supply exceeds demand
Not applicable
Author

thank you. that trick works! 🙂

thanks again.