topic Loop In Qlikview in QlikView

Loop In Qlikview

rahulsingh12 — Tue, 09 Jan 2018 13:19:12 GMT

Hi,

I have a query regarding the loop in Qlikview.

I have the data as follow

Month Value

Jan 10

Feb 11

Mar 0

Apr 0

May 10

Jun 12

What I want is to get the value as the last Month's value if the current month's value is 0

So If Mar's Value is 0 it should take Feb's value=11

If Apr's Value is 0 it should go to Feb's Value as Mar's value is also 0.

I know to make it dynamic we will have to loop through the data.

How do we do the looping here.

Regards

Re: Loop In Qlikview

sunny_talwar — Tue, 09 Jan 2018 13:27:02 GMT

Try this

LOAD Month,

Value,

If(Value = 0, Peek('NewValue'), Value) as NewValue

FROM ...

Re: Loop In Qlikview

sunny_talwar — Tue, 09 Jan 2018 13:29:27 GMT

Here is a sample script

Table:

LOAD Month(Date#(Month, 'MMM')) as Month,

Value,

If(Value = 0, Peek('NewValue'), Value) as NewValue

INLINE [

Month, Value

Jan, 10

Feb, 11

Mar, 0

Apr, 0

May, 10

Jun, 12

];

Re: Loop In Qlikview

rahulsingh12 — Wed, 10 Jan 2018 07:08:30 GMT

Hi Sunny,

Thanks for the reply.

Stockcode,Year,Month,Value

1,2012,Jan, 10

2,2012 Feb, 11

2,2012,Mar, 0

2,2012,Apr, 0

3,2012,May, 10

3,2012,Jun, 12

4,2012,Dec,12

4,2013,Jan,0

3,2013,Feb,0

3,2013,Mar,0

I have increased the complexity a little with addition of StockCode and Year addition.

Now I want the value based on the combination of Stockcode year and month.

So the logic I am looking for is when the StockCode and Year and Month matches to previous value and if the current month's value is 0 take the previous Value in Current month's value.

Now if Jan 2013 for any stockcode is having 0 value it should go to the 2012 Dec for same stock code and get the value from that month for Jan 2013 for that StockCode.

Re: Loop In Qlikview

sunny_talwar — Wed, 10 Jan 2018 09:01:01 GMT

Try this in that case

Table:

LOAD Stockcode,

Date(Date#(Year&Month, 'YYYYMMM'), 'MMM-YYYY') as MonthYear,

Value,

Year,

Month

INLINE [

Stockcode, Year, Month, Value

1, 2012, Jan, 10

2, 2012, Feb, 11

2, 2012, Mar, 0

2, 2012, Apr, 0

3, 2012, May, 10

3, 2012, Jun, 12

4, 2012, Dec, 12

4, 2013, Jan, 0

3, 2013, Feb, 0

3, 2013, Mar, 0

];

FinalTable:

LOAD *,

If(Stockcode = Previous(Stockcode) and Value = 0, Peek('NewValue'), Value) as NewValue

Resident Table

Order By Stockcode, MonthYear;

DROP Table Table;