Get record from a table which is next date from current date

[Rsaiq]

Hi All,

I have log file and I am tracking when the file is going to fail.

So,in my dataset i have some assumptions that files are going to fail on a particular  date. 

So,I need to check the next date when the file is going to fail from current date e.g.

For  ID=1

I need to add record in new table for date   04/01/2024 because it is next date  from current date as 04/01/2023 is passed.

For ID=2

I need to add record in new table for date   22/12/2023 because it is next date  from current date as 22/12/2022 is passed.

And we don't need to consider 22/12/2024 as before this date we have 22/12/2023.

Below is the expected result.

Thanks in advance

[rubenmarin · Accepted solution]

Hi, with the peek() function and a sorted table you can also do this, it's very simila to this post: https://community.qlik.com/t5/New-to-Qlik-Sense/Get-value-on-max-date-by-ID/td-p/2025856

in this case the order by date doesn't have to be descendant, and a few more checks with peek():

If(ID=Peek(ID) and Peek(Status)='Failed' and Status='To Fail',1,0) as isNextToFail