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

[#1]

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

[mike_garcia]

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

Message was edited by: Miguel Garcia