topic Re: Date Format Issue in QlikView

Date Format Issue

brijesh1991 — Fri, 06 Dec 2013 12:22:26 GMT

Hi All,

I had a table:

ID, Date1, Date2

1, 22/10/2013, 15/09/2013

2, 15/12/2013, 17/10/2012

I want to take difference between Date1 And Date2.

Because of Date format of Date1 and Date2 is mixed with Date format and text format.

So I am not getting difference between them.

Is there anyway to convert the text format into Date format, so that I can take the difference between both dates?

All your suggestions will be welcomed!

Brijesh

swuehl — Fri, 06 Dec 2013 12:24:42 GMT

Use the date interpretation function date#() with an appropriate format code, like

Date#(FIELD, 'MM/DD/YYYY') as DateField,

brijesh1991 — Fri, 06 Dec 2013 12:47:39 GMT

Thanks Swuehl,

When I am taking difference on front end(in a text box) as you said:

Date#(Date1,'DD/MM/YYYY')-Date#(Date2,'DD/MM/YYYY') it's coming fine if I will select a particular ID.

But I want the Difference at script level: i did following scripting, but not getting result.

MainTAB:

Load ID,Date#(Date1,'DD/MM/YYYY') as Date1, Date#(Date2,'DD/MM/YYYY') as Date2 from table1.xls;

NewTAB:

Load ID, Date1, Date2,

Date#(Date1,'DD/MM/YYYY')-Date#(Date1,'DD/MM/YYYY') as DateDiff Resident MainTAB;

drop table MainTAB;

Is there any problem into script?

Fri, 06 Dec 2013 12:53:22 GMT

Interval(Date(Date#(Date1,'DD/MM/YYYY'),'DD/MM/YYYY') - Date(Date#(Date2,'DD/MM/YYYY'),'DD/MM/YYYY'))

swuehl — Fri, 06 Dec 2013 12:53:44 GMT

Date#(Date1,'DD/MM/YYYY')-Date#(Date1,'DD/MM/YYYY') as DateDiff Resident MainTAB;

Here you are subtracting Date1 field from Date1 field, which will result in zero.

You already interpreted your date values before, so

MainTAB:

LOAD ID,

Date1,

Date2,

Date1- Date2 as DateDiff;

Load ID,Date#(Date1,'DD/MM/YYYY') as Date1, Date#(Date2,'DD/MM/YYYY') as Date2 from table1.xls;

should do.