Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
stewart_lancast
New 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
New Contributor III

Re: STORE statement rounding decimal values in csv

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

8 Replies
MVP
MVP

Re: STORE statement rounding decimal values in csv

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

Re: STORE statement rounding decimal values in csv

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

Re: STORE statement rounding decimal values in csv

I have also tried wrapping

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

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

Not applicable

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

stewart_lancast
New Contributor III

Re: STORE statement rounding decimal values in csv

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

bwisenosimenkg
Valued Contributor

Re: STORE statement rounding decimal values in csv

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

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;

stewart_lancast
New Contributor III

Re: STORE statement rounding decimal values in csv

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

Community Browser