Data Load transformation

[#1]

I have a data set that includes goals and actuals for multiple campaigns.  The way the data is listed is all in one row based on the Employee ID#.  The data is listed in columns labeled as: STR Q1 Goal, STR Q2 Goal, STR Q1 Actual, STR Q2 Actual...  I need to identify each column as referencing the Campaign (STR, LTR, Catering, etc), Quarter (Q1, Q2, Q3, Q4) and Component (Goal, Weight, Actual) in order to make my dashboard be functional.

Can someone help me?  This is only my 2nd time working with a load script and the first time I didn't need to transform much data other than the dates.

Thank you!

-Jackie

[Nicole-Smith]

So, let me get this straight...

Your data looks like this:

And you want it to look like this:

Then load script like this should do the trick:

Data:

CrossTable (Type,Value)

LOAD * INLINE [

Employee ID#,STR Q1 Goal,STR Q2 Goal,STR Q1 Actual,STR Q2 Actual

1,20,20,30,30

2,50,50,40,40

];

DataFinal:

LOAD [Employee ID#],

     subfield(Type,' ',1) as Campaign,

     subfield(Type,' ',2) as Quarter,

     subfield(Type,' ',3) as Component,

     Value

RESIDENT Data;

DROP TABLE Data;

An example file is also attached.