Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
LaureDenivelle
Partner - Contributor III
Partner - Contributor III

Rename multiple similare columns from Excel File in LOAD script

Hi

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 ?

Labels (3)
8 Replies
veidlburkhard
Creator III
Creator III

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:

MapFieldNames.jpg

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

LaureDenivelle
Partner - Contributor III
Partner - Contributor III
Author

Hi thanks for your answer.

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

I'll try this.

 

LaureDenivelle
Partner - Contributor III
Partner - Contributor III
Author

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

];

LaureDenivelle
Partner - Contributor III
Partner - Contributor III
Author

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.

LaureDenivelle
Partner - Contributor III
Partner - Contributor III
Author

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.

LaureDenivelle
Partner - Contributor III
Partner - Contributor III
Author

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). 

Brett_Bleess
Former Employee
Former Employee

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Saravanan_Desingh

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
];

commQV55.PNG