topic Crosstable Challenge with two types of columns in App Development

Crosstable Challenge with two types of columns

marcel_olmo — Fri, 10 Dec 2021 20:33:39 GMT

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.

Re: Crosstable Challenge with two types of columns

Saravanan_Desingh — Sat, 11 Dec 2021 04:38:13 GMT

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;

Re: Crosstable Challenge with two types of columns

marcel_olmo — Sat, 11 Dec 2021 11:35:19 GMT

Thanks @Saravanan_Desingh that was it. Regards, Marcel.