Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
I have an input table like this :
And I'd like to have an output table like this :
How can I do it? Maybe with crosstable function? In my real input table I have more than 10 dimensions and more than 5 years, so I have to do it somehow in an automatic way.
I've attached an excel with the example.
Best regards, Marcel.
Try this,
tab1:
LOAD * INLINE [
Id, City, Dimension1 2018, Dimension1 2017, Dimension2 2018, Dimension2 2017
39250, City1, 41.0, 40.8, 20.6, 20.7
39251, City2, 42.5, 42.7, 16.6, 16.5
];
tab1X:
CrossTable(Dimension, Value, 2)
LOAD * Resident tab1;
tab2:
NoConcatenate
LOAD Id, City, SubField(Dimension,' ',1) As Dimension,
SubField(Dimension,' ',2) As Year, Value
Resident tab1X;
Drop Table tab1, tab1X;
Try this,
tab1:
LOAD * INLINE [
Id, City, Dimension1 2018, Dimension1 2017, Dimension2 2018, Dimension2 2017
39250, City1, 41.0, 40.8, 20.6, 20.7
39251, City2, 42.5, 42.7, 16.6, 16.5
];
tab1X:
CrossTable(Dimension, Value, 2)
LOAD * Resident tab1;
tab2:
NoConcatenate
LOAD Id, City, SubField(Dimension,' ',1) As Dimension,
SubField(Dimension,' ',2) As Year, Value
Resident tab1X;
Drop Table tab1, tab1X;
Thanks @Saravanan_Desingh that was it. Regards, Marcel.