Date formats Question

[kkkumar82]

Hi all,

I have a question regarding the date formats. I have a txt file where I have records with different date formats, to my strange one of the date formats is validated by Qlikview and it is showing this in the right side of the list box but the other date formats the same list box is showing in the left side.

Just curious , according to my knowledge Qlikview shouldn't identify dates from text or Csv files unless we don't use Date#.

Please correct me if I am wrong?

Iam using Qv11.20 SR9.

Attaching the qvw and also the txt file used.

[Gysbert_Wassenaar]

Qlikview will use the format of the first record it can recognize as a date (usually the date format that's the same as you defined as your document default) to process the other records. You can use the alt function to specify alternative formats so you can process dates in different formats. See Tip 8 on page 7 of this document: QlikView Date fields

[Colin-Albert]

There are a couple of useful posts on date formatting here:

Why don’t my dates work?

Get the Dates Right

[sunny_talwar]

Try this:

LOAD EMPLOYEE_ID,

     FIRST_NAME,

     LAST_NAME,

     EMAIL,

     PHONE_NUMBER,

     Date(Alt(HIRE_DATE, Date#(HIRE_DATE, 'DD-MMM-YYYY'), Date#(HIRE_DATE, 'DD/MM/YYYY'))) as HIRE_DATE,

     JOB_ID,

     SALARY,

     COMMISSION_PCT,

     MANAGER_ID,

     DEPARTMENT_ID

FROM

[emp1.txt]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

[kkkumar82]

Thanks all for your speedy answers.

So Gysbert you say because my default format is DD - MM - YYYY  in edit script , that is why QV identified it as date.

thanks

Kiran Kumar

[kkkumar82 · Accepted solution]

All,

What I understand from Gysbert is it all depends on the variable "SET DateFormat='DD- MM- YY'; "  because my default format is DD-MM-YYYY, Qlikview was able to recognize it as date and others as strings, I tried changing the formats to other it was working as expected.

Thanks Gysbert once again.

Regards

Kiran