STORE statement rounding decimal values in csv

[stewart_lancast]

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

[jonathandienst]

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.

[stewart_lancast]

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)

[stewart_lancast]

I have also tried wrapping

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

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

[#5]

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

[stewart_lancast]

thanks @alexberry

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

[simenkg]

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;

[#8]

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;

[stewart_lancast · Accepted solution]

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

[IuliaM]

Hi All,

this solution also worked: Round(yourexpression, 0.0001) but not always! for some reason if you don't use a different alias for the columns than the original expression it does not work. if you change the name, it works.