topic Comparing two Dates in QlikView

Comparing two Dates

Mon, 08 Feb 2016 05:32:20 GMT

Hi All

I have a table

Table:

Case	First_Time	Next_time
Emp1	Null	25/1/2016
Emp2	16/1/2016	13/1/2015
Emp3	null	19/1/2016
Emp3	null	20/1/2016
Emp3	22/1/2016	23/1/2016

My senario is if i have my First_time as null then i should use Next_time

If i have First_time then i should First_time

If i have First_time as null use Next_time and later at any time i got the First_time i should use only Next_time, i dont want to change again to First_time

My output is

Case	First_Time	Next_time	Final_time
Emp1	Null	25/1/2016	25/1/2016
Emp2	16/1/2016	13/1/2015	16/1/2016
Emp3	null	19/1/2016	19/1/2016
Emp3	null	20/1/2016	19/1/2016
Emp3	22/1/2016	23/1/2016	19/1/2016

Re: Comparing two Dates

PradeepReddy — Mon, 08 Feb 2016 10:04:47 GMT

Load

Case,

First_Time,

Next_Time,

if(isnull(First_Time),Next_Time,First_Time) as Final_Time

from Souce_Table;

On which basis the data will change in First_Time field?

Re: Comparing two Dates

sunny_talwar — Mon, 08 Feb 2016 10:13:52 GMT

Try this also:

Table:

LOAD Case,

First_Time,

Next_time,

If(Case = Peek('Case'), Peek('Final_time'), If(Upper(First_Time) = 'NULL', Next_time, First_Time)) as Final_time

FROM

[https://community.qlik.com/thread/204578]

(html, codepage is 1252, embedded labels, table is @1);

Re: Comparing two Dates

Wed, 10 Feb 2016 09:51:20 GMT

Hi sunny your code has worked out but i have another senario which is causing the problem the below is the table

Thanks

EMP	FIRST_TIME	Min_time	Oput_put
A123	11/24/2015 0:00	11/3/2015 11:08	11/24/2015 0:00
B234
B234
B234	1/10/2016 0:00	12/13/2015 16:07	12/13/2015 16:07
C123		12/6/2015 7:15	12/6/2015 7:15
C123
C123
C123
C123
C123
D234
D234
D234	11/9/2015 0:00	10/28/2015 9:52	10/28/2015 9:52
F123	10/28/2015 9:52	11/9/2015 0:00	10/28/2015 9:52

Re: Comparing two Dates

sunny_talwar — Wed, 10 Feb 2016 11:39:55 GMT

What is the issue? Would you be able to elaborate?

Re: Comparing two Dates

Wed, 10 Feb 2016 11:51:12 GMT

the issue is

if there is no FIRST_TIME make Min_time as out_put

if there is no FIRST_TIME and later came FIRST_TIME we should make Min_time as out_put

if there is FIRST_TIME and Min_time make FIRST_TIME as out_put (even though we got the FIRST_TIME later)

but i'm facing issue for B234 customer where we are getting the FIRST_TIME later

Regards