RENAME Fields using a mapping table with in a loop

[meakerk_mrkl]

Hi,

Can anyone clarify whether there is a known issue with trying to create a mapping load or using Rename Fields (using a mapping load) within a loop.

I am trying to load multiple QVD's each with different field names in different cases and conforming these filed names using the Rename fields function

et vTrickyCount = NoOfRows('Tricky');

For z = 1 To $(vTrickyCount)-1
LET tCtable = FieldValue('TableName',$(z));
LET tCQVD = FieldValue('QVDName',$(z));

// Create mapping load data
Map_Table:
Mapping LOAD Distinct
A,
B
FROM
[$(ExtractQVD)MappingTable.qvd] (qvd);

//Load each table into QVD to enable the capture of metadata for a later stage
$(tCtable):
NoConcatenate
LOAD
*
FROM
[$(ExtractQVDStg1)$(tCQVD)] (qvd);

RENAME Fields using Map_Table;

STORE $(tCtable) INTO [$(ExtractQVDStg2)$(tCtable).QVD] (qvd) ;

Next z

I dont get any errors when running the script above, but neither do I get the desired output which is all field names being set to upper case

Interested to know if anyone has any ideas 

[marcus_sommer · Accepted solution]

In general your approach should work but several things are noticable and might be the reason for the unexpected behaviour.

Your mapping load is included within the loop - it's not necessary and might cause issues - this means just move it before the loop. Also you should check if it's the right mapping table and if the order of the fields is correct - the first field needs to be the lookup-value and the second the return-value.

Another problem could occur through your picking-logic in the loop which is fieldvalue() but it should be peek(). The reason is that fieldvalue() reads the distinct symbol-tables and not the data-table and could therefore return the wrong value respectively a wrong association between your two queries (tCtable + tCQVD).

Further I miss a drop statement for the loaded/renamed/stored tables. If two different fields should be renamed to a single field the call will be ignored (according to the help it creates no error) because it's an invalid call (not from the mapping point of view but from the renaming one).

- Marcus