topic Re: How to insert a column into table? in QlikView

How to insert a column into table?

Fri, 20 Nov 2020 18:02:30 GMT

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!

Re: How to insert a column into table?

Thu, 20 Nov 2014 13:12:17 GMT

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]

Re: How to insert a column into table?

Thu, 20 Nov 2014 13:14:18 GMT

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

Re: How to insert a column into table?

Thu, 20 Nov 2014 13:20:51 GMT

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?

Re: How to insert a column into table?

Thu, 20 Nov 2014 13:23:30 GMT

This mapping should have reordered it.

I don't think I understand what you mean?

Re: How to insert a column into table?

Peter_Cammaert — Thu, 20 Nov 2014 13:27:30 GMT

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

Re: How to insert a column into table?

Anonymous — Thu, 20 Nov 2014 14:48:21 GMT

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

Re: How to insert a column into table?

Thu, 20 Nov 2014 15:02:34 GMT

Hi Sriharsha,

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

Re: How to insert a column into table?

Thu, 20 Nov 2014 15:10:44 GMT

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:

Re: How to insert a column into table?

Thu, 20 Nov 2014 15:14:55 GMT

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.

Re: How to insert a column into table?

Thu, 20 Nov 2014 15:17:02 GMT

I know this, but my problem is that I want to make a crosstable after I added the column 'States' to the table. To make a crosstable I need to set the Qualifier Fields, which are the first columns of the table.

Re: How to insert a column into table?

Anonymous — Thu, 20 Nov 2014 15:20:14 GMT

Hi Mark

Is there any reason why you are looking for particular order because it doesnt matter for Qlikview

If you want to storing the table then you can use like wise

store table Cities ,states,Resident,Income rsident Citties into test.qvd(qvd); or test.csv(csv);

Regards

Harsha

Re: How to insert a column into table?

Thu, 20 Nov 2014 15:23:58 GMT

Yes, the reason is that I want to make a crosstable after I added the column 'States' to the table. To make a crosstable I need to set the Qualifier Fields, which are the first columns of the table.

Maybe I should just store the table, reorder it, load it again as a crosstable. But I hope that there is a way to do it in one script

Re: How to insert a column into table?

Thu, 20 Nov 2014 15:43:11 GMT

Within the crosstable, just reorder the load of the fields there?

Crosstable(xxx,YYY,3)

Load

States,

Cities,

Residents,

Income

Resident xxxx;

The crosstable will use the order from the Load

Hope that helps

Joe

Re: How to insert a column into table?

Thu, 20 Nov 2014 15:53:38 GMT

Thanks Joe! I used this code before:

Crosstable(xxx,YYY,3)

LOAD

Resident xxxx

However, my code is much more compact (because in my real dataset I have lots of fields), if I want to have the table in this specific order I have to do it like you said.

Re: How to insert a column into table?

Thu, 20 Nov 2014 17:04:07 GMT

Yea with that code the order is set as you say, glad to be of help though