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;
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;
All the fields are still showing up as table.field instead of their new field name when loaded
Are you using QUALIFY in your script? Is there a reason you use this?
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);
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);
I added that, but now it is only loading the first table, and still has the wrong names
Just by adding the trace?
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
That is so strange... would you be able to share a sample?