Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Black_Hole
Creator II
Creator 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
Frank_Hartmann
Master II
Master II

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
Frank_Hartmann
Master II
Master II

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

 

 

Black_Hole
Creator II
Creator II
Author

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.

Frank_Hartmann
Master II
Master II

try this:

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

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

Black_Hole
Creator II
Creator II
Author

Hi @Frank_Hartmann,

I try your last solution and this works perfectly!

Many thanks for your help.