Renaming Fields using a Mapping Table

[etrotter]

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;

[sunny_talwar]

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;

[etrotter]

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

[sunny_talwar]

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

[etrotter]

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

[sunny_talwar]

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

[etrotter]

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

[sunny_talwar]

Just by adding the trace?

[etrotter]

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

[sunny_talwar]

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