Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a field 'Date' and Interval which determines my Next_Check_Date,
Date | Interval(days) |
---|---|
20/5/2014 | 700 |
21/5/2014 | 30 |
22/5/2014 | 3 |
I have to apply the following logic to find out my Next_Check_Date
for(Date+Interval<today(),
(Date+ Interval) + Interval ) and so on
This should give my Next_Check_Date,
Thanks
*silly*
Thanks a lot C.A!! worked like magic!!
I don't think an Iteration is necessary at all in this case:
Date(Today()-Mod(Today()-Date-1, Intervall)+1) as Next_Check_Date
should do as well, possibly even more efficient.
Hope this helps
Regards
Marco
Hi,
sorry, wrong sign.
This should be better:
LOAD Date,
[Interval(days)],
Date(Today()-Mod(Today()-Date-1, [Interval(days)])-1) as Next_Check_Date
FROM
[http://community.qlik.com/thread/130355]
(html, codepage is 1252, embedded labels, table is @1, filters(
Remove(Row, Pos(Top, 6)),
Remove(Row, Pos(Top, 5))
));
hope this helps also
regards
Marco
Hi Marco,
Thanks a lot it Works partially, it was my mistake to give my requirements incomplete.
I want the next interval check date that should just cross the current day.
eg. if my Date value was 20/8/2014 with interval 30, then my next check date should be 20/9/2014.
for now this script works for me, although its quite unoptimized.
Iteration:
Load
Date,
date(Date+ (Interval * iterNo())) AS Next_Check_Date
resident Cons
while date(Date+ ((Interval)* iterNo()))-today()<Interval ;
Iteration2:
Load Date,
Next_Check_Date as NextCheck
Resident Iteration
where Next_Check_Date>Today();