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: 
etrotter
Creator II
Creator II

Renaming Fields using a Mapping Table

Hi, I am trying to rename fields in my table using a mapping table, I didn't want to use apply map because of its limitations so I am trying to do it manually. It is currently loading but it is not renaming the the fields, is there something wrong with my script? In loading the original fields I used Qualify so they are showing up Table.Field. Please let me know if you have any ideas:

       

        MappingTable:

Load

('$(vTable)' & '.' & Field) AS FieldName,

        Rename

FROM [lib://AttachedFiles/FieldMap.xlsx]

(ooxml, embedded labels, table is $(vTable);

       

        Let j = 1;

For j = 1 to noofrows('MappingTable')

Set vOldFieldName = peek(‘FieldName(j)-1, ‘MappingTable’);

Set vNewFieldName = peek(‘Rename’, $(j)-1, ‘MappingTable’);

Rename Field [$(vOldFieldName)] to [$(vNewFieldName)];

Next j;

14 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

You can skip the whole loop thing and just do:

Rename Fields USING MappingTable;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

etrotter
Creator II
Creator II
Author

I was originally using a mapping table, but there are a lot of limitations with it. You have to have distinct values in both the original field and the rename field, otherwise it doesn't rename. I was trying to avoid this issue by doing it manually

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi Erica,

I'm not clear what you mean by having to have distinct values. Could you show some example data to illustrate your point?

-Rob

etrotter
Creator II
Creator II
Author

In one of my tables User, I assign

ProfileId as %ProfileID

In the next table Profile, I want to assign Id as %ProfileID, but the name is only reassigned in the User table because it came first.

I ran only the profile table and all the names got reassigned correctly but when I run it with the other tables it no longer works.

I found this on the Qlik website:

"The Rename Field statement is positioned after the Load statement, and may also use a mapping table for ease of maintenance. The one caveat is that you cannot rename two fields with the same name using the Rename Field statement. If attempted, only the first instance of the renaming will take place; all the other instances will be ignored."

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

But don't you face the same restriction -- can't rename to an existing fieldname -- with your looping code?

-Rob