topic Re: Subtract Dates with Different Formats in QlikView

Subtract Dates with Different Formats

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have 2 columns in the Oracle DB having dates with different formats.

First date(Header1) is MM/DD/YYYY

Second Date(Header2) is DD-Mon-YYYY

Ex:

Header 1	Header 2
6/5/2018	20-Jun-18
6/2/2018	1-Jul-18

In Qlikview editor i am loading the dates in the below manner from SQL statement so that the dates appear in the standard format for display purpose and this is coming up fine.

TO_CHAR(TO_DATE(BILL_DATE, 'DD-Mon-YY'),'DD-MON-YYYY') as ACTUAL_BILLING_DATE,

TO_CHAR(ALARM_DATE, 'DD-MON-YYYY') as ALARM_DATE,

Now, in a straight table chart, i want to subtract he dates and get the difference in days only. I tried Date#,Interval and other functions, but nothing seems to be working. Any idea what expression needs to be used?

Thanks

Taher

Re: Subtract Dates with Different Formats

vishsaggi — Thu, 07 Jun 2018 22:10:33 GMT

I would create two more fields with date#() and get int values like

LOAD *,

Num(Floor(Date#(ACTUAL_BILLING_DATE, 'DD-MON-YYYY))) AS Header1,

Num(Floor(Date#(ALARM_DATE, 'DD-MON-YYYY))) AS Header2

;

SELECT Field1,

Field2,

TO_CHAR(TO_DATE(BILL_DATE, 'DD-Mon-YY'),'DD-MON-YYYY') as ACTUAL_BILLING_DATE,

TO_CHAR(ALARM_DATE, 'DD-MON-YYYY') as ALARM_DATE

FROM yoursourcetable;

Then just use header2 - header1.

Re: Subtract Dates with Different Formats

Anonymous — Thu, 07 Jun 2018 22:38:35 GMT

Hi,

I tried this. The editor script got reloaded successfully, but the difference didn't work.

It shows a - in the expression.

what i did:

LOAD*,

Num(Floor(Date#(ACTUAL_BILLING_DATE, 'DD-MON-YYYY'))) as Date1,

Num(Floor(Date#(ALARM_MON_YR_DT, 'DD-MON-YYYY'))) as Date2

;

SELECT:

TO_CHAR(TO_DATE(BILL_DATE, 'DD-Mon-YY'),'DD-MON-YYYY') as ACTUAL_BILLING_DATE,

TO_CHAR(ALARM_DATE, 'DD-MON-YYYY') as ALARM_MON_YR_DT,

Reloaded and it was succesfuull.

Then went to Straight table, added the below in calculated dimension:

=Date2-Date1

the column just shows a '-'

Re: Subtract Dates with Different Formats

sergio0592 — Thu, 07 Jun 2018 22:41:09 GMT

Hi, in your load script you can try with :

date#(SubField(Header2,'-',1)&'/'&date(date#(SubField(Header2,'-',2),'MMMM'),'MM')&'/'&SubField(Header2,'-',3),'DD/MM/YY')-

date#(SubField(Header1,'/',2)&'/'&SubField(Header1,'/',1)&'/'&right(SubField(Header1,'/',3),2),'DD/MM/YY') as duration

Re: Subtract Dates with Different Formats

Anonymous — Thu, 07 Jun 2018 22:54:18 GMT

HI Jean

I selected the 2 date columns as it is and displayed them in the sheet and this is how they are appearing:

I copied below expression in StraightTable calculated dimension:

=date#(SubField(BILL_DATE,'-',1)&'/'&date(date#(SubField(BILL_DATE,'-',2),'MMMM'),'MM')&'/'&SubField(BILL_DATE,'-',3),'DD/MM/YY')-date#(SubField(ALARM_DATE,'/',2)&'/'&SubField(ALARM_DATE,'/',1)&'/'&right(SubField(ALARM_DATE,'/',3),2),'DD/MM/YY')

But its not working.

Re: Subtract Dates with Different Formats

Anonymous — Thu, 07 Jun 2018 23:16:29 GMT

Hey Jean,

Your solution worked. Thanks a Lot

Taher

Re: Subtract Dates with Different Formats

Anonymous — Fri, 08 Jun 2018 07:22:43 GMT

HI Taher,

Ex:

Header 1	Header 2
6/5/2018	20-Jun-18
6/2/2018	1-Jul-18

First date(Header1) is MM/DD/YYYY

Second Date(Header2) is DD-Mon-YYYY

on top of SQL statement , write Load * , Alt (Header1, Date(Date#(Header1,'MM/DD/YYYY)) ,Date(Date#(Header1,'DD-MMM-YYYY)) ) AS _Header1,

Alt (Header2, Date(Date#(Header2,'MM/DD/YYYY)) ,Date(Date#(Header2,'DD-MMM-YYYY)) ) AS _Header2;

Re: Subtract Dates with Different Formats

vishsaggi — Fri, 08 Jun 2018 15:48:54 GMT

Can you share a sample where you have this issue. Usually Date#() with Num(Floor()) should help in this scenario, but need some sample so that i can give you and expr that works fine.