topic Re: date minus with another date in QlikView

date minus with another date

inam — Tue, 21 Mar 2023 07:34:03 GMT

Hello community ;

please help me for below query.

input:-

BCD	DEPDATE & TIME
23-07-2021	2021-08-12 - 1705 / 2021-08-12 - 2030
28-03-2022	2022-04-06 - 1340
11-01-2023	2022-11-21 - 0645
06-01-2023	2022-07-12 - 0000 / 2022-7-12 - 0000

EXPECTED OUTPUT:-

BCD	DEPDATE & TIME	day(bcd-depate & time)
21-12-2021	2021-12-26 - 1705 / 2021-12-30 - 2030	9
28-03-2022	2022-04-06 - 1340	9
11-01-2023	2022-11-21 - 0645	-51

I WANT:-

1) I want BCD - DEPDATE & TIME

2) if getting negative number replace negative number with zero(0) like -51 replace with zero.

3) BCD - (LAST DATE OF DEPDATE & TIME (Right side) )

4) I used this query but didn't get answer.

date(Right([DEPDATE &TIME],10),'DD-MM-YYYY')- date([BOOKING CREATION DATE],'DD-MM-YYYY') as days,

if i change (Right) with (left) i get answer but they minus left side date

Re: date minus with another date

Marijn — Tue, 21 Mar 2023 13:42:22 GMT

floor(date#(right([DEPTDATE&TIME],17),'YYYY-MM-DD - HHmm'))
-
floor(date#([BCD],'DD-MM-YYYY'))

Re: date minus with another date

inam — Wed, 22 Mar 2023 04:58:02 GMT

I apply the logic but day(bcd-depate & time) column is blank. not getting any values in day(bcd-depate & time) column

Re: date minus with another date

inam — Wed, 22 Mar 2023 05:31:08 GMT

its working now but how to set 0 if i found negative number.

Re: date minus with another date

Marijn — Wed, 22 Mar 2023 08:04:13 GMT

if(
floor(date#(right([DEPTDATE&TIME],17),'YYYY-MM-DD - HHmm')) - floor(date#([BCD],'DD-MM-YYYY')) < 0,0,
floor(date#(right([DEPTDATE&TIME],17),'YYYY-MM-DD - HHmm')) -
floor(date#([BCD],'DD-MM-YYYY'))

)

Re: date minus with another date

inam — Wed, 22 Mar 2023 10:09:22 GMT

Thanks A lot .its working fine but i have one doubt

some time my depdate & time is : like (13:00) or some time is like(1300) i..so i write 17 or 18 in the code

BCD	DEPDATE & TIME	days
13-02-2023	11-02-2023 - 0630	-2 but i get (0) (correct according to query)
03-03-2023	2023-03-01 - 13:00	but here date is not 0 beacuse of this colon 13:00 (here total length is 18 not 17)

how i solve this type format beacuse some times i getting colon and sometimes not .

Re: date minus with another date

Marijn — Thu, 23 Mar 2023 14:00:07 GMT

if(
floor(date#(right(purgechar[(DEPTDATE&TIME],':'),17),'YYYY-MM-DD - HHmm')) - floor(date#([BCD],'DD-MM-YYYY')) < 0,0,
floor(date#(right(purgechar([DEPTDATE&TIME],':'),17),'YYYY-MM-DD - HHmm')) -
floor(date#([BCD],'DD-MM-YYYY'))

)

Re: date minus with another date

inam — Fri, 24 Mar 2023 07:01:02 GMT

Thanks marijn 😊

Re: date minus with another date

igizarblehic — Wed, 29 Mar 2023 08:24:23 GMT

Thanks for sharing your knowledge.

ballsportsgames