How to insert a column into table?

[#1]

Hi all,

I've loaded multiple fields from an Excel-file with information about several cities into a table 'cities'.

Something like this:

Cities:

LOAD

Cities,

Residents,

Income

FROM [file1.xlsx]

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:

LEFT JOIN(Cities)

LOAD

Cities,

States

FROM [file2.xlsx]

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!

[#2]

As long as there is only one state for each city then you can use applymap:

Lookup_States:

mapping

load

Cities,

States

FROM [file2.xlsx]

Cities:

LOAD

Cities,

applymap('Lookup_States', Cities) as States,

Residents,

Income

FROM [file1.xlsx]

[#3]

Although you can reorder any table in the front end no matter what the load order was.

[#4]

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?

[#5]

This  mapping should have reordered it.

I don't think I understand what you mean?

[Peter_Cammaert]

Add these after your existing LOAD statements:

NewCities:

NOCONCATENATE

LOAD Cities, States, Residents, Income

RESIDENT Cities;

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.

Best,

Peter

[Anonymous]

Hi

you can even do right join insted of left join

Cities:

LOAD

Cities,

Residents,

Income

FROM [file1.xlsx]

Right Join (Cities)

LOAD

Cities,

States

FROM [file2.xlsx]

Regards

Harsha

[#8]

Hi Sriharsha,

I tried this, but the result is still a table with 'States' as last column..

[#9]

If you are just trying to create the order in a front end Qlikivew table you can just add the fields one at a time in the order you lik or select the load order button:

[#10]

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.