pivot data in load script

Anonymous — Tue, 15 Nov 2016 10:33:39 GMT

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.

topic pivot data in load script in App Development