Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Contributor II

Difference between two dates YYMMDD

Hello all,

I would like to know if it's possible to calculate the difference between two dates in a condition IF.

For example: IF (20190911-20180911)> 1 year THEN '90%'.

 

Please could you tell me if it's possible to do that in a LOAD Statement.

Many thanks in advance for your help.

Labels (3)
1 Solution

Accepted Solutions
Highlighted
Honored Contributor II

Re: Difference between two dates YYMMDD

or maybe this:

Load *, if(date(date#(Date1,'YYYYMMDD'),'YY/MM/DD')-date(date#(Date2,'YYYYMMDD'),'YY/MM/DD')>365,'90%','other') as New,
date(date#(Date1,'YYYYMMDD'),'YY/MM/DD') as Date3,
date(date#(Date2,'YYYYMMDD'),'YY/MM/DD') as Date4;
LOAD * INLINE [
    Date1, Date2
    20190911, 20180911
    20190911, 20180411
    20190911, 20180910
];
DROP Fields Date1,Date2; RENAME Fields Date3 to Date1; RENAME Field Date4 to Date2

View solution in original post

5 Replies
Highlighted
Honored Contributor II

Re: Difference between two dates YYMMDD

try like this:

 

 

Load *, if(date#(Date1,'YYYYMMDD')-date#(Date2,'YYYYMMDD')>365,'90%','other') as New;
LOAD * INLINE [
    Date1, Date2
    20190911, 20180911
    20190911, 20180411
    20190911, 20180910
];

 

 

Highlighted
Contributor II

Re: Difference between two dates YYMMDD

Hello @Frank_Hartmann ,

Thank you for your quick reply.

Before to try your solution, I have a problem with the format of the date.

Indeed, in my DB, the dates have the format YYMMDD. But, in my excel files, the dates have the format YY/MM/DD.

Is it possible to add this element in your expression below:

if(date#(Date1,'YYYYMMDD')-date#(Date2,'YYYYMMDD')>365,'90%','other') 

In order to standardize the format of the dates and to make properly the difference.

Thank you in advance for your help.

Highlighted
Honored Contributor II

Re: Difference between two dates YYMMDD

try this:

if(date(date#(Date1,'YYYYMMDD'),'YY/MM/DD')-date(date#(Date2,'YYYYMMDD'),'YY/MM/DD')>365,'90%','other') 
Highlighted
Honored Contributor II

Re: Difference between two dates YYMMDD

or maybe this:

Load *, if(date(date#(Date1,'YYYYMMDD'),'YY/MM/DD')-date(date#(Date2,'YYYYMMDD'),'YY/MM/DD')>365,'90%','other') as New,
date(date#(Date1,'YYYYMMDD'),'YY/MM/DD') as Date3,
date(date#(Date2,'YYYYMMDD'),'YY/MM/DD') as Date4;
LOAD * INLINE [
    Date1, Date2
    20190911, 20180911
    20190911, 20180411
    20190911, 20180910
];
DROP Fields Date1,Date2; RENAME Fields Date3 to Date1; RENAME Field Date4 to Date2

View solution in original post

Highlighted
Contributor II

Re: Difference between two dates YYMMDD

Hi @Frank_Hartmann,

I try your last solution and this works perfectly!

Many thanks for your help.