Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
];
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.
try this:
if(date(date#(Date1,'YYYYMMDD'),'YY/MM/DD')-date(date#(Date2,'YYYYMMDD'),'YY/MM/DD')>365,'90%','other')
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