how to give number to repeated values in load script

[#1]

Hello,

I have a table where i have two fields. Period and Week. I want to give a unique number to each week within the period. the number should start from 1 for each period

Original Table is below

Period     Week

1               w1

1               w2

1               w3

1               w4

2               w5

2               w6

2               w7

2               w8

2               w9

i want the above table to become like

Period     Week     week_number

1               w1          1

1               w2          2

1               w3          3

1               w4          4

2               w5          1

2               w6          2

2               w7          3

2               w8          4

2               w9          5

Can any one suggest how can i do this in load script.

Arif

[#2 · Accepted solution]

try this:

tmp:

LOAD * INLINE [

    Period, Week

    1, w1

    1, w2

    1, w3

    1, w4

    2, w5

    2, w6

    2, w7

    2, w8

    2, w9

    3, w10

    4, w11

    4, w12

];

data:

load if(Period=Previous(Period),Peek(Counter)+1,1) as Counter,

    Period,

    Week

resident tmp;

drop table tmp;

Regards

[nagaiank]

Attached is a solution which works.

[#4]

Thanks to both of you. Both solutions were helpful and my code started working. now i have another small problem . Can you also help in this case please. I have two columns..week and item_demand. for each week, I want to calculate the sum of demands for next four weeks. as given below

Original table

week     item_demand

1               5

2               6

3               1

4               4

5               8

6               3

7               4

I want the above table to be like

week     item_demand     sum_for_bext_four_weeks

1               5                              19

2               6                              16

3               1                              19

4               4

5               8

6               3

7               4

one more thing. If there are less than four weeks below the current row, then stop calculating it or just keep null there. there should not be any value then. or we can just put another column which indicates if we have less then four rows below the current one or more than four rows below the current row

Thanks

Arif

[mike_garcia]

There is also the Autonumber Function for assigning a consecutive number to the field values. It starts with zero as well.

[#6]

you should habituate to create other thread for other questions.

You can use a chart with week as dimension and this expression:

IF(rowno()>=count(total item_demand)-3

    ,''

    ,rangesum(below(sum(item_demand),1,4)))

Hope this help you

[#7]

hello,

thanks for the help. actually u r right. i realized this too and pösted another thread for this. actually, i need the solution for second pröblem using load script. can u post the solution using load script. u can either reply to this thread or the other thread that posted

thanks,

arif