how to use rangesum or while loop for my problem??

Sat, 11 Jun 2011 12:23:28 GMT

Hello,

I have a table A, which has three fields like below

Period Week Demand

1 1 ......

1 2 ......

1 3 .......

1 4

1 5

1 6

2 7

2 8

2 9

2 10

2 11

2 12

3 13

3 14

3 15

3 16

3 17

3 18

The above table has already been loaded in script. Now i want to make another resident table, where I have the Period, and the sum demand of first four week for each period

Table B

Period Sum of Demands

1 ............

2 .............

3 ..............

probably the code should be something like below

B:
LOAD PERIOD
     RANGESUM(BELOW(WEEK),1,4)
RESIDENT A

but its not working. I know how to do it in pivot table, but i want to do it in load script, since I have to make some further calculations on it in load script.

Can someone help please

Arif

Re: how to use rangesum or while loop for my problem??

mike_garcia — Sat, 11 Jun 2011 23:15:17 GMT

On Table A, add one field to be used as a Flag. For example

TableA:
Load 
      Period,
      Week,
      Amount,
      If(IsNull(Peek('Period')), 1,
          If(Period = Peek('Period') and Peek('Counter') < 4,
              Peek('Counter') + 1,
               If(Period = Peek('Period'), 0, 1))) as Counter
From Table A

(For the Peek to work correvtly the table must be ordered by Period and Week).

Then, on Table B, do:

TableB:
Load
      Period,
      Sum(Amount) as TotalAmount
Resident TableA
     Where Counter > 0
     Group By Period;

Mike

Message was edited by: Miguel Garcia

topic how to use rangesum or while loop for my problem?? in QlikView

how to use rangesum or while loop for my problem??

Re: how to use rangesum or while loop for my problem??