topic Re: Renaming Fields using a Mapping Table in App Development

Renaming Fields using a Mapping Table

etrotter — Thu, 31 Aug 2017 14:15:13 GMT

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;

Re: Renaming Fields using a Mapping Table

sunny_talwar — Thu, 31 Aug 2017 14:18:32 GMT

I have no tried this, but may be this

MappingTable:

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

Rename

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

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

For j = 1 to noofrows('MappingTable')

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

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

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

Next j;

Re: Renaming Fields using a Mapping Table

etrotter — Thu, 31 Aug 2017 14:21:15 GMT

All the fields are still showing up as table.field instead of their new field name when loaded

Re: Renaming Fields using a Mapping Table

sunny_talwar — Thu, 31 Aug 2017 14:24:49 GMT

Are you using QUALIFY in your script? Is there a reason you use this?

Re: Renaming Fields using a Mapping Table

etrotter — Thu, 31 Aug 2017 14:26:36 GMT

Yes, it is so that I can drop unwanted fields:

Qualify *;

$(vTable):

NoConcatenate

LOAD

Select *

From $(vTable);

UNQUALIFY *;

DropFields:

LOAD concat('$(vTable)' & '.' & Field, ', ') AS DropFields

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

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

WHERE Load = 'No';

Let vDropFields = Peek('DropFields', 0, 'DropFields');

Drop Table DropFields;

Drop Fields $(vDropFields);

Re: Renaming Fields using a Mapping Table

sunny_talwar — Thu, 31 Aug 2017 14:30:39 GMT

Can you add trace and see if there are values in these new variables

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

TRACE $(vOldFieldName);

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

TRACE $(vNewFieldName);

Re: Renaming Fields using a Mapping Table

etrotter — Thu, 31 Aug 2017 14:44:30 GMT

I added that, but now it is only loading the first table, and still has the wrong names

Re: Renaming Fields using a Mapping Table

sunny_talwar — Thu, 31 Aug 2017 14:56:12 GMT

Just by adding the trace?

Re: Renaming Fields using a Mapping Table

etrotter — Thu, 31 Aug 2017 15:47:15 GMT

I replaced

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

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

with

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

TRACE $(vOldFieldName);

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

TRACE $(vNewFieldName);

and that is what happened

Re: Renaming Fields using a Mapping Table

sunny_talwar — Thu, 31 Aug 2017 15:49:52 GMT

That is so strange... would you be able to share a sample?

Re: Renaming Fields using a Mapping Table

rwunderlich — Thu, 31 Aug 2017 15:57:13 GMT

You can skip the whole loop thing and just do:

Rename Fields USING MappingTable;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Renaming Fields using a Mapping Table

etrotter — Thu, 31 Aug 2017 16:08:42 GMT

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

Re: Renaming Fields using a Mapping Table

rwunderlich — Thu, 31 Aug 2017 17:41:23 GMT

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

Re: Renaming Fields using a Mapping Table

etrotter — Thu, 31 Aug 2017 18:03:38 GMT

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

Re: Renaming Fields using a Mapping Table

rwunderlich — Thu, 31 Aug 2017 18:30:35 GMT

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

-Rob