Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
You can skip the whole loop thing and just do:
Rename Fields USING MappingTable;
-Rob
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
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
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."
But don't you face the same restriction -- can't rename to an existing fieldname -- with your looping code?
-Rob