8 Replies Latest reply: Sep 23, 2015 9:51 AM by Stewart Lancaster RSS

    STORE statement rounding decimal values in csv

    Stewart Lancaster

      HI all,

       

      I have an application that runs a simple SQL query, the dataset returns a simple data table.

       

      The script then goes on to store this table in QVD format and csv format - however the CSV has no decimal places whilst the application and the QVD output does in face hace 2 decimal places.

       

      Could anyone let me know if they have any ideas how to overcome this?

       

      I have tried changing the document settings as well as the table setting for number format - but am willing to try (or re-try) anything!

       

      thank you in advance.

       

      Stewart

       

      Script:

       

      SET ThousandSep=',';

      SET DecimalSep='.';

      SET MoneyThousandSep=',';

      SET MoneyDecimalSep='.';

      SET MoneyFormat='£#,##0.00;-£#,##0.00';

      SET TimeFormat='hh:mm:ss';

      SET DateFormat='DD/MM/YYYY';

      SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

      SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

      SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

       

      [connection string removed]

       

      //Variables: [filepaths amended for publication]

      Let vDate = Date(Now(), 'YYYY.MM.DD');

      let vPath = '\QlikView\CLTOT $(vDate).qvd (qvd)';

      let vPath2 = '\2015\CLTOT $(vDate).csv (txt)';

       

      //Query [SQL query amended for publication]

      CLTOT:

      SELECT

        SUM (p.cltot) cltot,

        TO_CHAR (SYSDATE, 'dd/mm/yyyy') rundate

      FROM .portfolio p,

           clients c,

      GROUP BY p.owner;

       

      //Save down hard copy

      STORE CLTOT into $(vPath);

      STORE CLTOT into $(vPath2);

        • Re: STORE statement rounding decimal values in csv
          Jonathan Dienst

          One thing I can suggest - go to Document Properties | Number. Find the field cltot and set the format to fixed with two decimal places. And make sure that the option to survive reload is checked.

            • Re: STORE statement rounding decimal values in csv
              Stewart Lancaster

              thanks Jonathan

               

              just tried the above and the csv output still has no decimals.

               

              I have also tried the same settings in the table box properties and the output is still rounded (in the csv only, not in the qvd)

                • Re: STORE statement rounding decimal values in csv
                  Stewart Lancaster

                  I have also tried wrapping

                   

                  num(CLTOT, '#,##.00', '.' , ',')

                   

                  around the value before the store statement but that causes an error

                    • Re: STORE statement rounding decimal values in csv

                      Hi Stewart,

                       

                      This may help.  From this example, you can use LOAD RESIDENT to reformat the value to dec(8,2) or whatever you want and then store the result.

                       

                      Regards, Alex

                       

                      YOURTAB:

                       

                      LOAD * INLINE [theLbl, theVal

                      a,12313.2312321

                      b,31.231321

                      c,29.99

                      d,132.321 ];

                       

                      YOURTAB2:

                       

                      LOAD theLbl, num(theVal,'#.##') as theVal2 RESIDENT YOURTAB;

                       

                      store YOURTAB2    into [\\path\file.csv]      (txt);

                        • Re: STORE statement rounding decimal values in csv
                          Stewart Lancaster

                          thanks @Alex Berry

                           

                          sorry if i am being slow - i have tried to use the above and here is what i have

                           

                          //Variables: [filepaths amended for publication]

                          Let vDate = Date(Now(), 'YYYY.MM.DD');

                          let vPath = '\QlikView\CLTOT $(vDate).qvd (qvd)';

                          let vPath2 = '\2015\CLTOT $(vDate).csv (txt)';

                           

                          //Query [SQL query amended for publication]

                          CLTOT:

                          SELECT

                            SUM (p.cltot) cltot,

                            TO_CHAR (SYSDATE, 'dd/mm/yyyy') rundate

                          FROM .portfolio p,

                               clients c,

                          GROUP BY p.owner;

                          CLTOT2:

                          load * INLINE [num(CLTOT, '#,##.00'),RUNDATE];

                          //Save down hard copy

                          STORE CLTOT into $(vPath);

                          STORE CLTOT2 into $(vPath2);

                           

                          this isnt importing any values other than the headings at present.

                           

                          i have also tried:

                           

                          CLTOT2:

                          load  [num(CLTOT, '#,##.00'),RUNDATE] resident CLTOT;

                           

                          could anyone provide any further suggestions

                            • Re: STORE statement rounding decimal values in csv

                              Hi Stewart, your CLTOT2 is not quite what I'm suggesting.

                               

                              In my example, I wrote:

                               

                              LOAD

                                   theLbl,

                                   num(theVal,'#.##') as theVal2

                              RESIDENT YOURTAB;

                               

                              And you are using:

                               

                              load * INLINE [

                                   num(CLTOT, '#,##.00'),

                                   RUNDATE];


                              In this case, wouldn't your CLTOT2 be something like this?

                               

                              CLTOT2:

                               

                              LOAD

                                   num(cltot, '#,##.00') as cltot,

                                   rundate]

                              RESIDENT CLTOT;

                    • Re: STORE statement rounding decimal values in csv
                      Simen Kind Gulbrandsen

                      You can try adding Text() around the field.

                      //Query [SQL query amended for publication]

                      CLTOT:

                      load text(cltot) as cltot,

                      rundate;

                      SELECT

                        SUM (p.cltot) cltot,

                        TO_CHAR (SYSDATE, 'dd/mm/yyyy') rundate

                      FROM .portfolio p,

                           clients c,

                      GROUP BY p.owner;

                      • Re: STORE statement rounding decimal values in csv
                        Stewart Lancaster

                        Hi All

                         

                        i have found a workaround - changing the sql query seems to pull the correct values to both the QVD and the CSV

                         

                        i still think the issue I was experiencing must have been to do with some of the formatting (or some other setting). but this seems to force through the values:

                         

                        TO_CHAR (SUM (h2.cltot), '99999D99' ) cltot,

                         

                         

                        Script:

                         

                        SET ThousandSep=',';

                        SET DecimalSep='.';

                        SET MoneyThousandSep=',';

                        SET MoneyDecimalSep='.';

                        SET MoneyFormat='£#,##0.00;-£#,##0.00';

                        SET TimeFormat='hh:mm:ss';

                        SET DateFormat='DD/MM/YYYY';

                        SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

                        SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

                        SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

                         

                        [connection string removed]

                         

                        //Variables: [filepaths amended for publication]

                        Let vDate = Date(Now(), 'YYYY.MM.DD');

                        let vPath = '\QlikView\CLTOT $(vDate).qvd (qvd)';

                        let vPath2 = '\2015\CLTOT $(vDate).csv (txt)';

                         

                        //Query [SQL query amended for publication]

                        CLTOT:

                        SELECT

                          TO_CHAR (SUM (h2.cltot), '99999D99' ) cltot,

                          TO_CHAR (SYSDATE, 'dd/mm/yyyy') rundate

                        FROM .portfolio p,

                            clients c,

                        GROUP BY p.owner;

                         

                        //Save down hard copy

                        STORE CLTOT into $(vPath);

                        STORE CLTOT into $(vPath2);

                         

                        thank you to @Simen Kind Gulbrandsen, @Alex Berry & @jonathan dienst for your kind suggestions