topic Re: Date formatting Issue while saving data in .csv in QlikView

Date formatting Issue while saving data in .csv

pra_kale — Tue, 13 Sep 2016 17:49:53 GMT

Hi,

I am facing an issue while saving data into .csv through store command. The date format is not remain constant means for few rows date will be DD/MM/YYYY where as for some rows date will be MM/DD/YYYY. To resole the issue i am manually changing the dates by opening a csv file.

Is there any way by which I can over come this issue.

Thanks in advance.

Re: Date formatting Issue while saving data in .csv

MindaugasBacius — Tue, 13 Sep 2016 18:11:53 GMT

Do you have a master calendar?

Could you share a script and provide us with a data you have?

Re: Date formatting Issue while saving data in .csv

Digvijay_Singh — Tue, 13 Sep 2016 18:39:05 GMT

Is the date showing single format in Qlikview? I found one possibility that if you don't use any format(Date function) function while loading your data, you may have two formats appearing in your listbox and the same continues while saving data. The very first format function makes all other values to follow the first format and all values formatted in single format.

See the sample, if you remove the outer date function, the saved file will carry two formats.

Table1:

Load Date(alt(Date#(Date,'MM/DD/YYYY'),Date#(Date,'DD/MM/YYYY'))) as Date,Value;

Load * inline [

Date, Value

01/13/2016, 200

15/01/2016, 400 ];

Store Table1 into Table1.xlsx (txt)

Re: Date formatting Issue while saving data in .csv

pra_kale — Sat, 17 Sep 2016 12:07:26 GMT

Hi,

Thanks Digvijay and Mindaugas for your help..

Below given is the script. Strange part is below given test script is now giving proper result.

But, similar kind of date formatting when i am using iin reality it is not giving proper results. Means for few rows date formats is DD-MM-YY and for few rows date format is MM-DD-YY.

I don't know why this is happening.

vdate = num(date((monthstart(addmonths(Today(),-1)))));

vdate1 =num(date((monthend(addmonths(Today(),-1)))));

LOAD Policy,

date(Date,'DD/MM/YYYY') as New_Date,

Premium

FROM

(ooxml, embedded labels, table is Sheet1);

Left Join

LOAD PolicyNUmber as Policy,

FROM

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

LOAD * Resident A where num(New_Date) >= $(vdate) and num(New_Date) <= $(vdate1);

DROP Table A;

STORE B into E:\Qlikview\Date wise.csv(txt);

Thanks in advance

Re: Date formatting Issue while saving data in .csv

Digvijay_Singh — Sun, 18 Sep 2016 05:26:58 GMT

I think as long as you have statement date(Date,'DD/MM/YYYY') as New_Date (at the start of loading this field), it will have all values of New_Date in DD/MM/YYYY format, can you share the value of environment variable DateFormat? Appearing at the top of the script as SET DateFormat='DD-MM-YYYY';

Re: Date formatting Issue while saving data in .csv

Digvijay_Singh — Sun, 18 Sep 2016 05:27:59 GMT

Also can you share the sample where it is giving double format output. As you said now this script is showing correct results.