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 · Accepted solution]

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!

[nataliad18]

This worked - thank you! Very creative

It is not perfect as some of the countries that start for N i wanted to come before countries starting with A, but at least they are all grouped together and that is a win. Thank you!

[FinnCharlton]

@nataliad18 To combat this, you can add numbers before the city names rather than using the country names. Here I've made a lookup table in a text input tool where you can specify the order of the countries:

[snow2608]

I have a similar problem. Before the crosstab I use a formula to prefix the question names (these become the column names at the end) with the survey id and field id. I have union after the crosstab. When i add the dynamic rename between the crosstab and union it gives me a warning "multiple fields named "Birthdate". The duplicate was renamed.". This messes up the final result.  If i don't prefix the question and don't do the dynamic rename then I get all of the columns as I expect except they are all in alphabetic order, which i don't want.