topic Re: Rename fields using mapping table in QlikView

Rename fields using mapping table

effinty2112 — Wed, 23 Mar 2016 16:21:05 GMT

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

Re: Rename fields using mapping table

Gysbert_Wassenaar — Wed, 23 Mar 2016 16:29:20 GMT

Use the alias option in the load statements:

LOAD

Customers.ID as [Account Number],

...

Re: Rename fields using mapping table

swuehl — Wed, 23 Mar 2016 16:31:00 GMT

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.

Re: Rename fields using mapping table

effinty2112 — Wed, 23 Mar 2016 16:36:24 GMT

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.

Re: Rename fields using mapping table

Gysbert_Wassenaar — Wed, 23 Mar 2016 16:37:32 GMT

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

Re: Rename fields using mapping table

effinty2112 — Wed, 23 Mar 2016 16:40:49 GMT

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.

Re: Rename fields using mapping table

Gysbert_Wassenaar — Wed, 23 Mar 2016 16:44:38 GMT

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.

Re: Rename fields using mapping table

MarcoWedel — Thu, 24 Mar 2016 00:21:50 GMT

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

Re: Rename fields using mapping table

MarcoWedel — Thu, 24 Mar 2016 18:15:43 GMT

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

Re: Rename fields using mapping table

Anonymous — Sun, 09 Oct 2016 02:56:19 GMT

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.

Re: Rename fields using mapping table

swuehl — Sun, 09 Oct 2016 08:18:13 GMT

It would be better to add these information to your original thread.

I don't think you want to rename your fields, you need to create something like a canonical name field:

Canonical Date

It could look like (instead of the new name field, you can use a mapping approach, if your new names don't catch all old names):

Original:

LOAD Primary,

[Ticket Number],

Secondary

FROM

(ooxml, embedded labels, table is [base data]);

LinkTable:

CROSSTABLE (NameOrder, Name)

LOAD [Ticket Number], Primary, Secondary

Resident Original;

NewNames:

LOAD [Old Name] as Name,

[New Name]

FROM

(ooxml, embedded labels, table is [mapped names]);

Re: Rename fields using mapping table

Anonymous — Mon, 10 Oct 2016 06:07:11 GMT

I have atachted my sample file . File concatenates the extract from multiple sources i.e. RTC, Problems, Incidents and TR.

A mapping sheet has been created as the names are different in each of these extracts so as to map these to the actual names that are required.

In the RTC extract there are 2 fields i.e. Primary and Secondary Owner against one ticket. So each ticket is resolved by 2 people. I have to put a list box with the names so when the user selects on a particular name the count of the tickets resolved by that person is calculated irrespective of the name appearing in the primary or secondary column should appear. The count should take into consideration both the cols i.e.Primary and Secondary