Pivoting data in data load editor

[AlonF]

 Hey everyone,

I have a table with these fields:

Worker Name,

HourCost_2014,

    MonthCost_2014,

    MonthCost_2014,

    MonthCost_2015,

    HourCost_2016,

    MonthCost_2016,

    HourCost_2017,

    MonthCost_2017

This table contains the hourly and monthly cost of each worker, for every year.

I would like to turn it to be like this:

Worker name

Year

Hour_Cost

Month_Cost

can you give me any advise? i read a bit about cross table an i'm not sure how to use it properly for this one.

Thanks!

[Nicole-Smith]

Load script like this should do the trick: 

/* Cross table data */
Temp:
CrossTable(Field, Value, 1)
LOAD * 
INLINE [
	Worker Name, HourCost_2014, MonthCost_2014, HourCost_2015, MonthCost_2015, HourCost_2016, MonthCost_2016, HourCost_2017, MonthCost_2017
	Jane Doe, 100, 200, 300, 400, 500, 600, 700, 800
	John Doe, 800, 700, 600, 500, 400, 300, 200, 100
];

/* Format data */
Final:
LOAD [Worker Name],
	 SUBFIELD([Field], '_', 2) AS [Year],
	 [Value] AS [Hour_Cost]
RESIDENT Temp
WHERE [Field] LIKE 'HourCost*';

OUTER JOIN (Final)
LOAD [Worker Name],
	 SUBFIELD([Field], '_', 2) AS [Year],
	 [Value] AS [Month_Cost]
RESIDENT Temp
WHERE [Field] LIKE 'MonthCost*';

DROP TABLE Temp;

First we use CrossTable to get the data into two columns.  Then we have to split out the year and type from the original field names.  The above code will return a result that looks like this:

Worker Name	Year	Hour_Cost	Month_Cost
Jane Doe	2014	100	200
Jane Doe	2015	300	400
Jane Doe	2016	500	600
Jane Doe	2017	700	800
John Doe	2014	800	700
John Doe	2015	600	500
John Doe	2016	400	300
John Doe	2017	200	100