topic Re: Rename multiple similare columns from Excel File in LOAD script in QlikView

Rename multiple similare columns from Excel File in LOAD script

LaureDenivelle — Sat, 16 Nov 2024 19:09:55 GMT

I have about 100 Excel files to load to create a customer referential but some columns are similare but not the same.

In order to provide QlikView to create 10 different columns instead of one, is it possible in Qlik to specify the column name like :

For example, when I load my files I have multiple "Address" column like "Address 1", "Adresse 1", "ADRESSE 1","ADDRESS 1", "Address"....

So I want to rename all those column to a single one name [Address 1]

Something like IF Fieldname like "Ad*ss*" then [Address 1]

Is it possible in QlikView ?

Re: Rename multiple similare columns from Excel File in LOAD script

veidlburkhard — Thu, 06 Feb 2020 11:14:44 GMT

Hi Laure,

what you can do in QlikView is to make a list of your different Adress field name and create an excel file mapping table

like this:

In your script you do the loading and renaming with this mapping:

MapFieldNames:
Mapping
LOAD OldFieldName,
NewFieldName
FROM
MapFieldName.xlsx
(ooxml, embedded labels, table is Tabelle1);

T1:
LOAD * INLINE [
ADRESS1
München
Stuttgart
Düsseldorf
Berlin
Hamburg
Hannover

];

RENAME Fields using MapFieldNames;

With this, at last, you will have [Adresse 1] as your new address field.

Hope this helps

Burkhard

Re: Rename multiple similare columns from Excel File in LOAD script

LaureDenivelle — Thu, 06 Feb 2020 11:18:50 GMT

Hi thanks for your answer.

That's a good idea (and a good start :)).

I'll try this.

Re: Rename multiple similare columns from Excel File in LOAD script

LaureDenivelle — Thu, 06 Feb 2020 12:36:51 GMT

I don't know if I'm missing something but nothing happened when I used the "RENAME Fields using MapFieldNames;" even with your example.

Here is my Excel File. I put the exact same code you told me and to see if it's working I put this to fit an example in my Excel file (1st line) :

T1:
LOAD * INLINE [
addres
München
Stuttgart
Düsseldorf
Berlin
Hamburg
Hannover

];

Re: Rename multiple similare columns from Excel File in LOAD script

LaureDenivelle — Thu, 06 Feb 2020 13:00:11 GMT

Ok the problem was that I can't put an existing field name as new field name in my mapping.

But still, some of the fields didn't change even with the mapping.

For example "Address" and "address" didn't change. I'm searching why.

Re: Rename multiple similare columns from Excel File in LOAD script

LaureDenivelle — Thu, 06 Feb 2020 13:14:57 GMT

In fact I'm using the "Rename Field using Applymap" after all the concatenation between my different Excel files.

So maybe Qlik manages to change the first field it finds but then can't change the next one because the field name is already existing.

That means I need to put my rename field before the final table is created.

Re: Rename multiple similare columns from Excel File in LOAD script

LaureDenivelle — Fri, 14 Feb 2020 14:18:14 GMT

I did'nt find the solution yet.

I just think the answer is not in QlikView (not the proper software to use for this king of thing).

Re: Rename multiple similare columns from Excel File in LOAD script

Brett_Bleess — Wed, 04 Mar 2020 21:50:18 GMT

Check out the following Design Blog post, it should give you some further guidance to help you get things working, the other post is the correct way to go, but hopefully this post will help you get things sorted out given the additional info in it.

https://community.qlik.com/t5/Qlik-Design-Blog/Mapping-and-not-the-geographical-kind/ba-p/1463192

Regards,
Brett

Re: Rename multiple similare columns from Excel File in LOAD script

Saravanan_Desingh — Fri, 15 May 2020 01:49:49 GMT

One solution might be.

tab1: CrossTable (AddressHdr, Address1) LOAD * INLINE [ ID, Address 1, Adresse 1, ADRESSE 1, ADDRESS 1, Address, Address 2, Adresse 2, ADRESSE 2, ADDRESS 2, Address 3 707, some text1, some text2, some text3, some text4, some text5, some text6, some text7, some text8, some text9, some text10 919, other text1, other text2, other text3, other text4, other text5, other text6, other text7, other text8, other text9, other text10 335, sample text1, sample text2, sample text3, sample text4, sample text5, sample text6, sample text7, sample text8, sample text9, sample text10 ];