topic Re: Date formats Question in QlikView

Date formats Question

kkkumar82 — Wed, 02 Mar 2016 17:35:44 GMT

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.

Re: Date formats Question

Gysbert_Wassenaar — Wed, 02 Mar 2016 17:37:37 GMT

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

Re: Date formats Question

Colin-Albert — Wed, 02 Mar 2016 17:41:47 GMT

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

Why don’t my dates work?

Get the Dates Right

Re: Date formats Question

sunny_talwar — Wed, 02 Mar 2016 17:43:04 GMT

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

Re: Date formats Question

kkkumar82 — Wed, 02 Mar 2016 17:55:37 GMT

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

Re: Date formats Question

kkkumar82 — Thu, 03 Mar 2016 04:57:43 GMT

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