Date formatting Issue while saving data in .csv

[pra_kale]

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.

[MindaugasBacius]

Do you have a master calendar?

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

[Digvijay_Singh]

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)

[pra_kale]

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

A:

LOAD Policy,

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

    Premium

FROM

(ooxml, embedded labels, table is Sheet1);

Left Join

LOAD PolicyNUmber as Policy,

    SA

FROM

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

B:

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

[Digvijay_Singh]

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

[Digvijay_Singh]

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