Crosstable Challenge with two types of columns

[marcel_olmo]

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.

[Saravanan_Desingh · Accepted solution]

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;

[marcel_olmo]

Thanks @Saravanan_Desingh  that was it. Regards, Marcel.