pivot data in load script

[Anonymous]

Hi,

I have a table like this:

RV:

LOAD * INLINE [

  DIM1, DIM2 , Week, SOH, TD

  A, A, 201645, 10, 5

  A, A, 201646, 5, 1

  A, A, 201647, 4, 3

  A, A, 201648, 2, 3

  B, A, 201645, 20, 5

  B, A, 201646, 15, 1

  B, A, 201647, 14, 3

  B, A, 201648, 12, 3

];

What I need is a final table with this structure:

  DIM1, DIM2 , Week, SOH, TD, Week1, Week2, Week3, Week4

  A, A, 201645, 10, 5, 5, 4, 1

  A, A, 201646, 5, 1, 4, 1, -2

  A, A, 201647, 4, 3, 1, -2, -2

  A, A, 201648, 2, 3, 1, 1, 1

  B, A, 201645, 20, 5, 15, 14, 11

  B, A, 201646, 15, 1, 14, 11, 8

  B, A, 201647, 14, 3, 11, 8, 8

  B, A, 201648, 12, 3, 9, 9, 9

For the group of dim1 and dim2, look in each week, calculate:

Week1: sum(SOH) - rangesum(after(sum(TD),1,1))

Week1: sum(SOH) - rangesum(after(sum(TD),1,2))

Week1: sum(SOH) - rangesum(after(sum(TD),1,3))

I can do this calculation in a diagram measure formular,

but that cost too must time.

I would like to calculate it in the load script.