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: 
stewart_lancast
Contributor III
Contributor III

STORE statement rounding decimal values in csv

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

1 Solution

Accepted Solutions
stewart_lancast
Contributor III
Contributor III
Author

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

View solution in original post

9 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
stewart_lancast
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

I have also tried wrapping

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

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

Not applicable

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
Contributor III
Contributor III
Author

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
Specialist
Specialist

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;

Not applicable

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III

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.