Dynamically Reorder Columns

nataliad18

Hi! Is it possible to dynamically reorder columns, instead of doing it manually in select tool?

I.e I have the following dataset originally:

Record ID   City             Country           Status

1                 London          UK                 OK

2                 Manchester    UK               In process

3                 Paris              France          Faulty

4                 Madrid          Spain              OK

5                Barcelona      Spain             Faulty

etc

I have done cross tab on this data, where i kept city as column headers, status as rows and values inside cells, which were count, i.e:

                       London        Paris     Madrid       Manchester  Barcelona

OK                     1                  3          2                  5                     2

In process          1                  2          5                  3                      1

Faulty                2                   1          1                  2                    3

(numbers here will not add up with first table as i did not put the whole table up in the first sample)

I now want to order the column headers grouped by Country, so that i.e UK countries would be after each other (London, manchester) as well as Spanish ones, and not across randomly. Is there any way to do this more dynamically? I realise i would somehow need to link it back to the original datafile, dont mind extra steps as long as its dynamic.

FinnCharlton

Hi @nataliad18 , here is one approach. We rename the columns with a combination of country and city, for example 'UK_Manchester'. When we CrossTab, the fields are renamed in alphabetical order, grouping common countries together. We can then use string calculations to rename the columns. Hope this helps!

Dynamic Sort Columns.yxmd