Rename fields using mapping table

[effinty2112]

Hello,

I would like to rename my fields using a mapping table but QV is not allowing me to change a fieldname to one that already exists.

This is an extract from my mapping table:

OldName	NewName
Orders.AH_IDENT	Account Number
Customers.ID	Account Number

I want to create an association between these two fields but QV is not allowing this. The fieldname Customers.ID remains unchanged. Is there a neat way around this restriction?

Thanks for any help.

Andrew

[Gysbert_Wassenaar]

Use the alias option in the load statements:

LOAD

     Customers.ID as [Account Number],

     ...

[swuehl]

Renaming a field to an existing one means creating a key field, merging field values. This is more than just a plain renaming of values in the meta data. That's why it's not supported via RENAME.

Youo need to use another LOAD and rename using AS.

[effinty2112]

Hi Gysbert,

I did have all my fields renamed in this manner before and to spare the tedium of doing this in new applications working off the database I use day in day out renaming using a mapping table seemed to be a real time saver but I'm disappointed that I'm not allowed freedom to rename as I wish.

[Gysbert_Wassenaar]

You can use a mapping table for all fields, except those that you want to give the same name.

[effinty2112]

I guess there's no other way around it. It's a pity we can't inform QV of the mapping before the fields are created.

[Gysbert_Wassenaar]

You can rename all you want with mapping tables. But not with one mapping table and dropping fields or tables in between in your case. If you're going to store tables anyway to qvd then you can create different mapping tables, apply them, store the resulting tables and drop them and then move on to renaming fields in other tables with another map.

[MarcoWedel]

Hi,

one way to use your renaming table despite this restriction of rename field statements could be:

tabFieldRename:
LOAD Concat('['&OldName&'] as ['&NewName&']', ', ') as FldRenStr
INLINE [
    OldName, NewName
    Orders.AH_IDENT, Account Number
    Customers.ID, Account Number
];

LET vFldRenStr = Peek('FldRenStr');

DROP Table tabFieldRename;

ALIAS $(vFldRenStr);

table1:
LOAD RecNo() as Orders.AH_IDENT,
    Ceil(Rand()*20) as Value1
AutoGenerate 10;

table2:
LOAD RecNo() as Customers.ID,
    Ceil(Rand()*20) as Value2
AutoGenerate 10;

hope this helps

regards

Marco

[MarcoWedel]

or maybe more reusable as subroutine:

SUB AliasFieldsFromTable (tableName)
  LET vField1 = FieldName(1,tableName);
  LET vField2 = FieldName(2,tableName);
  tabTempFldRen:
  LOAD Concat('['&$(vField1)&'] as ['&$(vField2)&']', ', ') as FldRenStr
  Resident $(tableName);
  LET vFldRenStr = Peek('FldRenStr');
  DROP Table tabTempFldRen;
  ALIAS $(vFldRenStr); 
ENDSUB;

tabFieldRename:
LOAD * INLINE [
    OldName, NewName
    Orders.AH_IDENT, Account Number
    Customers.ID, Account Number
];

CALL AliasFieldsFromTable ('tabFieldRename');

table1:
LOAD RecNo() as Orders.AH_IDENT,
    Ceil(Rand()*20) as Value1
AutoGenerate 10;

table2:
LOAD RecNo() as Customers.ID,
    Ceil(Rand()*20) as Value2
AutoGenerate 10;

hope this helps

regards

Marco

[Anonymous]

Hi Marco

I have tried the below solution but its not working in my case. Will you be able to assist me in what changes I need to make for my script..I was going through the forum to search for the renaming of 2 fields to one name and I found this page.I had posted my query on the renaming of multiple fields earlier and I didn't share the base file.. I am attaching the base file to make my query easier to understand. What i need to find out is the count of tickets resolved by a person. I need to map the Primary and Secondary names to the mapping sheet and have these stored in a single col against the ticket numbers.