Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Comparing two Dates

  Hi All


I have a table

Table:

CaseFirst_TimeNext_time
Emp1Null25/1/2016
Emp216/1/201613/1/2015
Emp3null19/1/2016
Emp3null20/1/2016
Emp322/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

   

CaseFirst_TimeNext_timeFinal_time
Emp1Null25/1/201625/1/2016
Emp216/1/201613/1/201516/1/2016
Emp3null19/1/201619/1/2016
Emp3null20/1/201619/1/2016
Emp322/1/201623/1/201619/1/2016
5 Replies
PradeepReddy
Specialist II
Specialist II

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?

sunny_talwar

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

Capture.PNG

Not applicable
Author

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

Thanks

EMPFIRST_TIMEMin_timeOput_put
A12311/24/2015 0:0011/3/2015 11:0811/24/2015 0:00
B234
B234
B2341/10/2016 0:0012/13/2015 16:0712/13/2015 16:07
C123 12/6/2015 7:1512/6/2015 7:15
C123
C123
C123
C123
C123
D234
D234
D23411/9/2015 0:0010/28/2015 9:5210/28/2015 9:52
F12310/28/2015 9:5211/9/2015 0:00

10/28/2015 9:52

sunny_talwar

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

Not applicable
Author

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