Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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?
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);
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 |
What is the issue? Would you be able to elaborate?
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