I've loaded multiple fields from an Excel-file with information about several cities into a table 'cities'.
Something like this:
I have another Excel-file with two fields: 'States' and 'Cities'.
In order to link this file to the table, I used a Left Join:
This works well. The result is a table with columns 'Cities', 'Residents', 'Income', 'States'.
However, I want the same table but in another order, like: 'Cities', 'States', 'Residents', 'Income'.
Does anyone know how to link those files and get the field 'States' as the second column of the table?
Thanks in advance!
As long as there is only one state for each city then you can use applymap:
applymap('Lookup_States', Cities) as States,
Thanks for you quick reply!
Mapping seems to be a good solution to me. However, if it is possible to reorder my table that would be perfect.
Is it possible to reorder the table in the script, before I join it with some other tables?
Add these after your existing LOAD statements:
LOAD Cities, States, Residents, Income
DROP Table Cities;
RENAME Table NewCities TO Cities;
Although I should say that this is a pretty useless extension because the column order in an internal table is utterly unimportant. And when exporting data using a STORE statement, you can always specify a rearranged column list.
Thanks. It is not working yet because my datasets are way more complicated than my example, but I will keep trying.
I know that it seems a useless extension, but because I want to make a crosstable I need to have this field as one of the first columns, so I can choose 'States' as a Qualifier Field in the Crosstable.