Loop In Qlikview

[rahulsingh12]

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

[sunny_talwar]

Try this

LOAD Month,

     Value,

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

FROM ...

[sunny_talwar]

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

];

[rahulsingh12]

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.

[sunny_talwar]

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;