Dynamically add Measures in Pivot based on data

[rneelakantam]

Hi All,

I have my data as below:

LOAD * INLINE [
Field1, Field2, Field3, Sales,Year
AAA, CCC, GGG, 10,2000
AAA, CCC, HHH, 20,2001
AAA, DDD, JJJ, 30,2005
AAA, DDD, KKK, 40,2006
BBB, EEE, LLL, 50,2007
BBB, EEE, MMM, 60,2008
BBB, FFF, NNN, 70,2000
BBB, FFF, PPP, 80,2001
];

Requirement:

1) I need to create a Pivot which would have Year as my dimension (ROWS) and one new dimension (which consists of all Field1,Field2 & Field3 values) as COLUMNS

2) Also I want to create a dynamic logic such that if there is any new value added in any of the Fields( i.e Field1,2,3) It should populate the sum(Sales) directly without making any script changes.