How to batch rename fields to match other existing fields of other tables

[rdugg]

I have a table Z where i have several fields named  X.<name_of_a_property> and I want those fieldx to be renamed to  <name_of_a_property> which is a common name in a few of my other tables.

when I do my

Rename Field $(var1) to $(var2)

I get the error: "The name '<value of var2>' already exists".

which is crazy because that's exactly what I want to achieve, so that later I can have filters in my views which filters all tables.

The alternative is to recreate a new table "Load Resident Z" using a "as" on every fields after the "load" keyword so that I rename my fields.

Doing this is problematic because  I have to explicitly write the existing field name and new field name which I don't want to do. this have to be done programmatically because my new property names are a reduction of my actual ones

My properties X.<name_of_a_property> come from a table X where I used a "qulify *", then later after several join/where transformation I get my table Z that always has qualified names with the X table name in the property name.

Basically what I want is to rename my fields without having the <X.> qualification that was introduced ealier in my script (for a good reason).

So what's the good way to do that 🤯 ?

[marcus_sommer · Accepted solution]

Best practice is to avoid the qualifying because it's often causing a lot of problems without providing an equavivalent benefit.

If you want to remain by this approach you may change the order of your load-statements and loading your qualified-fields at first and performing then the renaming. Afterwards you could load the unqualified stuff.

Otherwise you will need the mentioned extra loads with any kind of renaming measurements because like you observed it's not possible to rename a field to an already existing field-name.

[Kushal_Chawda]

@rdugg  try below

// Data is the table name where X. fields are residing. Change the table name as required.

for i=1 to NoOfFields('Data')

let vFieldName = FieldName($(i),'Data');

Fields:
Load '$(vFieldName)' as FieldNameFrom
AutoGenerate 1;

Next

map_rename_fields:
Mapping Load FieldNameFrom,
                        SubField(FieldNameFrom,'.',2) as FieldNameTo
Resident Fields

where wildmatch(FieldNameFrom,'X.*');

Drop Table Fields;

Rename Fields using map_rename_fields;

Note: Put this code to the end of script or after loading 'Z' table in your example

[rdugg]

Ok, thanks I got it.

This comment about the limitation of rename field that cannot change the data model is exactly the wall i'm hitting.

The reason I used qualified fields is because i'm iteratively developing / checking my script and I don't want the data model to be tangled in my temporary steps with the "global table that gives input to my script.

That was I can introduce an exist script anyway, without doing any end of script cleaning and can visualize intermediate steps.

There should be a way to detach / attach some table to the data model without having to drop them and re-import them from a data source.

like a namespace system were the data model would be active in a namespace and you would be able move tables away/to it without having to drop/reload the table data.

[Kushal_Chawda]

@rdugg  I have added wildmatch filter in above reply to filter only X. fields

[marcus_sommer]

Keeping n intermediate evolution-steps within an application is the only scenario in which I use a qualifying - to avoid any associations with the final data-model - means it's always this/these single table(s).

If this isn't practicable enough and/or the evaluation should be done on consisting data and/or within another application I do store and concatenate the wanted steps by adding a date/timestamp + step-identificator to them.

Beside of this this what you want isn't possible because each field is a system-table which just stored distinct field-values + a binary index as link to the data-table(s). Two different fields must have an overlapping content (at least to the index) which made it's impossible to merge them - at least not without a reload.

[rdugg]

your solution doesn't work as I want.

If the "rename fields using" with a map_table works correctly, it is only if there are no fields already present in the data model with a name matching a FieldNameTo.

Which is not my case, because that was exactly what I was trying to achieve ( renaming to "converge" data in the data model).

one additional note: While the "Rename Fields To" ends the script with an error, the "Rename Fields using" doesn't rename problematic fields and silently continue which is a documentation bug because it's not documented in qlik sense documentation while it is in the qlik view one (as told in the limitation paragraph)

[Kushal_Chawda]

@rdugg  I am not sure what issue you are facing but rename field using should work as your fields are already stored in some table.  Mapping keyword is important in this case. So piece of code should be placed after the load of the table is completed. But anyway you got the answer so should be fine.