topic Re: STORE statement rounding decimal values in csv in QlikView

STORE statement rounding decimal values in csv

stewart_lancast — Tue, 22 Sep 2015 13:36:41 GMT

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

jonathandienst — Tue, 22 Sep 2015 13:44:28 GMT

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_lancast — Tue, 22 Sep 2015 14:09:30 GMT

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_lancast — Tue, 22 Sep 2015 14:16:39 GMT

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

Wed, 23 Sep 2015 11:05:51 GMT

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_lancast — Wed, 23 Sep 2015 12:22:22 GMT

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

Re: STORE statement rounding decimal values in csv

simenkg — Wed, 23 Sep 2015 12:25:58 GMT

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

Wed, 23 Sep 2015 13:35:09 GMT

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

stewart_lancast — Wed, 23 Sep 2015 13:51:10 GMT

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

Re: STORE statement rounding decimal values in csv

IuliaM — Wed, 04 Sep 2019 10:03:05 GMT

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.