topic Re: Iteration? in QlikView

Iteration?

Fri, 22 Aug 2014 06:34:02 GMT

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

Re: Iteration?

sushil353 — Fri, 22 Aug 2014 06:38:38 GMT

Hi,

You can try something below

for i=0 to Date(Today(),'DD/MM/YYYY')-(Date+Interval)

...

Next i;

didn't understood your statement. (Date+ Interval) + Interval ) for next check date.

HTH

Sushil

Re: Iteration?

CELAMBARASAN — Fri, 22 Aug 2014 06:45:26 GMT

You can try something like this

Load

Date,

Interval,

Date + (Interval * iterNo()) AS Next_Check_Date

FROM .....

While (Date + (Interval * iterNo())) < Today();

Re: Iteration?

Fri, 22 Aug 2014 07:15:24 GMT

I'll Explain, My Date Values should keep on incrementing according to the Intervals until it crosses the current date.

Hope its understood.

sushil rathore wrote:

Hi,

You can try something below

for i=0 to Date(Today(),'DD/MM/YYYY')-(Date+Interval)

...

..

Next i;

didn't understood your statement. (Date+ Interval) + Interval ) for next check date.

HTH

Sushil

Re: Iteration?

Fri, 22 Aug 2014 07:16:02 GMT

Ur logic is exactly what i want But its not working

Re: Iteration?

CELAMBARASAN — Fri, 22 Aug 2014 07:30:31 GMT

Can you post your script? So that I can take a look for the issue.

Re: Iteration?

Fri, 22 Aug 2014 08:59:16 GMT

I just took a sample ,

Int:

LOAD Date + (Interval * iterNo()) AS Next_Check_Date

INLINE [

Date, Interval

20/5/2014, 700

21/5/2014, 30

22/5/2014, 3

];

While (Date + (Interval * iterNo())) < Today();

Re: Iteration?

CELAMBARASAN — Fri, 22 Aug 2014 09:09:12 GMT

Both while and load statement are single statement. Don't split it

Int:

LOAD Date + (Interval * iterNo()) AS Next_Check_Date

INLINE [

Date, Interval

20/5/2014, 700

21/5/2014, 30

22/5/2014, 3

]

While (Date + (Interval * iterNo())) < Today();

Re: Iteration?

Fri, 22 Aug 2014 09:12:01 GMT

doesnt make a difference, I tried by taking resident load as well,

Re: Iteration?

CELAMBARASAN — Fri, 22 Aug 2014 09:23:45 GMT

Can you check whether the date format mentioned to the variable is same as that are in the inline table?

SET DateFormat='DD/MM/YYYY';

Re: Iteration?

Fri, 22 Aug 2014 09:32:19 GMT

*silly*

Thanks a lot C.A!! worked like magic!!

Re: Iteration?

MarcoWedel — Fri, 22 Aug 2014 11:47:21 GMT

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

Re: Re: Iteration?

MarcoWedel — Fri, 22 Aug 2014 13:39:52 GMT

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

Re: Iteration?

Sat, 23 Aug 2014 04:26:18 GMT

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