4 Replies Latest reply: May 29, 2013 3:39 AM by Rhea Pama RSS

    Inconsistent value format



      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.