topic Re: Rename Fields in QlikView

Rename Fields

jono19872002 — Sat, 16 Nov 2024 00:26:48 GMT

I have a Qlikview script which takes data from an SQL database and then takes these fields into several different tables for reports before storing all into reports.

The SQL db has the field names with an underscore and not space in the field names eg ACTIVITY_MONTH, I need the fields for my report to be [Activity Month] i.e. with a space.

Is there a way of dynamically updating the field name in a Qlikview script (I know I can individually write ACTIVITY_MONTH AS [Activity Month] - but there are a lot of fields and wanted to do this dynamically for more flexibility?

In my script happy to do this either when loading from a resident table or the final part to store into a csv

Re: Rename Fields

agigliotti — Mon, 22 Jun 2020 10:44:40 GMT

Hi,

you can do it through an external excel file using the script below:

/////
FieldMap:
mapping
LOAD
OldName,
NewName
FROM yourExcelFilePath
(biff, embedded labels, table is FieldMapping$);

Rename Fields using FieldMap;
/////

where "Old name" is your actual field name and "NewName" is your modified field name.
put the code at the end of your script.

I hope it can helps.

Re: Rename Fields

marcus_sommer — Mon, 22 Jun 2020 13:11:36 GMT

In addition to the mapping-suggestion to rename all fields at once which based on an Excel source you could use a nested loop with:

for i = 1 to nooftables()
let vTable = tablename($(i));
for ii = 1 to nooffields('$(vTable)')
RenameFields:
load fieldname($(ii), '$(vTable)'), capitalize(replace(fieldname($(ii), '$(vTable)'), '_', ' ')) autogenerate 1;
next
next

MapRenameFields: mapping load * resident RenameFields; drop tables RenameFields;

Rename Fields using MapRenameFields;

Depending on your real tables/fields you may need some further if-loops and/or where-clauses or similar stuff to skip some of them and/or to apply some more logic for adjustments/exceptions.

- Marcus