Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a data set in the format:
Reference Number, Date01, Date02,...., Date12, Qty01, Qty02,..., Qty12
How can I transpose/crosstab this data resulting in:
Reference Number, Date, Qty
Please see attached...
PFA, I assumed that Qty01 was related to Month01 and so on
PFA, I assumed that Qty01 was related to Month01 and so on
Hi,
another solution without hardcoding the field names could be:
tabInput:
CrossTable(ColNam, ColVal)
LOAD * INLINE [
ReferenceNumber, Month01, Month02, Month03, Month04, Month05, Month06, Month07, Month08, Month09, Month10, Month11, Month12, Qty01, Qty02, Qty03, Qty04, Qty05, Qty06, Qty07, Qty08, Qty09, Qty10, Qty11, Qty12
Abc01, 1/1/2014, 2/1/2014, 3/1/2014, 4/1/2014, 5/1/2014, 6/1/2014, 7/1/2014, 8/1/2014, 9/1/2014, 10/1/2014, 11/1/2014, 12/1/2014, 10, 0, 10, 0, 10, 0, 10, 0, 10, 0, 10, 0
Abc02, 3/1/2014, 4/1/2014, 5/1/2014, 6/1/2014, 7/1/2014, 8/1/2014, 9/1/2014, 10/1/2014, 11/1/2014, 12/1/2014, 1/1/2015, 2/1/2015, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100
Abc03, 6/1/2014, 7/1/2014, 8/1/2014, 9/1/2014, 10/1/2014, 11/1/2014, 12/1/2014, 1/1/2015, 2/1/2015, 3/1/2015, 4/1/2015, 5/1/2015, 500, 800, 1000, 1000, 1000, 1000, 1000, 1000, 1000, 1000, 1000, 1000
Abc04, 1/1/2014, 2/1/2014, 3/1/2014, 4/1/2014, 5/1/2014, 6/1/2014, 7/1/2014, 8/1/2014, 9/1/2014, 10/1/2014, 11/1/2014, 12/1/2014, 55, 55, 55, 55, 55, 55, 55, 55, 55, 55, 55, 55
Abc05, 4/1/2014, 5/1/2014, 6/1/2014, 7/1/2014, 8/1/2014, 9/1/2014, 10/1/2014, 11/1/2014, 12/1/2014, 1/1/2015, 2/1/2015, 3/1/2015, 55, 55, 55, 55, 55, 55, 55, 55, 55, 55, 55, 55
];
tabOutput:
Generic LOAD
ReferenceNumber,
Right(ColNam,2) as ID,
Left(ColNam,Len(ColNam)-2),
ColVal
Resident tabInput;
DROP Table tabInput;
hope this helps
regards
Marco