Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
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
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.
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 '-'
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
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.
Hey Jean,
Your solution worked. Thanks a Lot
Taher
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;
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.