I'm working on time-series data and want to convert multiple rows to column. I have tried using transpose and crosstab, but seem to me i'm doing something wrong. See below for sample record.
See output record below
Hi @hay_001
As mentioned above, isolate the headers and then join back to the data. The top piece gets the 5 header rows and flips them to be columns. Then it joins back to the dates and values.
Can you attach the data instead of screenshots? Typically you'll want to use a sample tool to isolate the top 4 rows, transpose those, and join them back to your dataset.
To accomplish what you're trying to do, you must apply two different treatments to your input file.
First 4 rows need different operations than the remaining ones.
I'd split the file (maybe using a RecordID and filtering the first four rows and the remaining ones).
Then start transposing & CrossTabing.
At the end, merge the two datastreams to get the desired output.
See attached
Hi @hay_001 ,
Find a solution attached.
@Luke_C, Awesome and thank you, that worked..
