Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

How to insert a column into table?

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!


15 Replies
Highlighted
Not applicable

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]

Highlighted
Not applicable

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

Highlighted
Not applicable

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?

Highlighted
Not applicable

This  mapping should have reordered it.

I don't think I understand what you mean?

Highlighted

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

Highlighted
Creator II
Creator II

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

Highlighted
Not applicable

Hi Sriharsha,

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

Highlighted
Not applicable

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:

Capture.PNG.png

Highlighted
Not applicable

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.