topic Re: Difference between two dates YYMMDD in QlikView

Difference between two dates YYMMDD

Black_Hole — Sat, 16 Nov 2024 02:31:01 GMT

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.

Re: Difference between two dates YYMMDD

Frank_Hartmann — Wed, 11 Sep 2019 07:34:50 GMT

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

Re: Difference between two dates YYMMDD

Black_Hole — Wed, 11 Sep 2019 07:46:06 GMT

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.

Re: Difference between two dates YYMMDD

Frank_Hartmann — Wed, 11 Sep 2019 07:59:54 GMT

try this:

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

Re: Difference between two dates YYMMDD

Frank_Hartmann — Wed, 11 Sep 2019 08:06:33 GMT

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

Re: Difference between two dates YYMMDD

Black_Hole — Thu, 12 Sep 2019 08:57:40 GMT

Hi @Frank_Hartmann,

I try your last solution and this works perfectly!

Many thanks for your help.