Renaming Fields - End User Document

[ralph_graham]

Hello,

I'm looking to convert database fields into more user friendly fields and have been able to extract the metadata out of the database so that I have the code to descriptions names and don't require, or want, a spreadsheet in the background to map field names against. My problem is that the normal method of mapping field names, as highlighted in How to Rename Fields does not work as I have a number of tables with a different field code but the same field name, see below:

TABLE	FIELD	FIELD NAME
Sales	ABCHDF	Year
Customer	NCJDFH	Year
Mileage	JSMDLS	Year

When I apply the method above it only applies the 'Year' description to one field and leaves the rest as the codes (i.e. NCJDFH). I read a suggestion from hic‌ and unfortunately it does not work (see below) as it leaves a load of orphan tables as MarcelArtzVlisco‌ found.

Aliases:

Load FromName, ToName From Aliases.qvd (qvd)

     Where Not Exists(FromName);

For vFieldNo = 0 to NoOfRows('Aliases') - 1

     Let vFromName = Peek('FromName',vFieldNo,'Aliases');

     Let vToName = Peek('ToName',vFieldNo,'Aliases');

     Alias [$(vFromName)] as [$(vToName)];

Next vFieldNo

If anyone has an alternative or solution please let me know.

Thanks,

Ralph

[Gysbert_Wassenaar]

You could create an excel file with a table like:

SourceTable	SourceField	TargetTable	TargetField
Sales	ABCDEF	SalesFact	Year
Sales	PQRS	SalesFact	Product
Sales	AJKSDG	SalesFact	%CustomerID
Sales	URTTY	SalesFact	Amount
Customer	JKLHDS	CustomerDim	%CustomerID
Customer	SDKJSHD	CustomerDim	Customer name
Customer	USDTHS	CustomerDim	Year

You can then load this table to create variables to use to load the data from the source files

LoadMap:

LOAD

  SourceTable,

  TargetTable,

  concat( '[' & SourceField & ']' & ' as ' & '[' & TargetField & ']', ',') as FieldList

FROM

  (biff, embedded labels, table is Sheet1$)

GROUP BY SourceTable, TargetTable

  ;

For i = 0 to NoOfRows('LoadMap') -1

  LET vSourceTable = peek('SourceTable',$(i),'LoadMap');

  LET vTargetTable = peek('TargetTable',$(i),'LoadMap');

  LET vFieldList =  peek('FieldList',$(i),'LoadMap');

  $(vTargetTable):

  LOAD

  $(vFieldList)

  FROM

  $(vSourceTable) (biff, embedded labels, table is Sheet1$)

  ;

Next

[ralph_graham]

Thanks for that. Ideally I want to get away from the spreadsheet model as that's what we currently use but suspect it might be the only option.

Ralph

[Gysbert_Wassenaar]

You could also create that table as an inline table in or in a text file. But you will need to use such a table unless you want to rename the fields manually. At the very least you'll need a mapping table to rename fields like in the blog post you referred to. The reason that didn't work is that once a field exists you can rename another field to the same name. It should work if you drop each table after renaming its fields (and storing the table to qvd so you can easily load it again later).