Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

sspe_dgs_com
Contributor II

Rename field in only one table

Hi,

I'm looking for a smart way to load a resident table but prefixing all the fields with a string.

As an example, I have loaded a Customer dimension, but now I'd like to create a SellToCustomer and a BillToCustomer dimension where all the fieldnames are prefixed with SellTo and BillTo.

Of course I can just write my load statement and specify all fields with an alias, but I was looking to a way to avoid this.

I have also looked at the RENAME FIELD statement, but that changes the name in all tables and not only in e.g. the BillTocustomer table. At least, I can't see that I can tell it to only change it in a specific table.

Using the QUALIFY feature could also be an option, but I don't want the "." in the field name, and I can't see that I can avoid that with the QUALIFY statement.

Anyone who have an idea to solve this?

Regards

Steen

19 Replies

Re: Rename field in only one table

May be check this out:

How to Rename Fields

Partner
Partner

Re: Rename field in only one table

My understanding of Field Rename is that this function applies to any fields  already loaded by the script.

Any Fields Loaded after invoking Field Rename() is not affected by the the function.

If you load from QVD instead of Resident Load and then perform a Field Rename for your SellTo, this should sort your 1st requirement.

Perform another QVD Load & Field Rename sequence for your BillTo.

sspe_dgs_com
Contributor II

Re: Rename field in only one table

Hi Sunny,

I have looked at this, but this is more or less the options I already know. HIC also mentions the RENAME FIELDS, which doesn't work in this case, since it renames fields in all tables - not only in one specific table..

Regards

Steen

sspe_dgs_com
Contributor II

Re: Rename field in only one table

Hi Jonathan,

We are not using QVD files at all, so that option I'd like to avoid. Also, it might be just as cumbersome as simply writing a new load statement and then just use "AS" to rename each field.

This is what I'm doing right now and then with a variable where I add the text that should prefix each field.

Regards

Steen

adamdavi3s
Honored Contributor

Re: Rename field in only one table

Hi Steen,

You can qualify, rename the one you want, then run a little loop to remove the qualification from all the others.

I am sure I wrote something similar for someone recently so I will try and dig it out.

adamdavi3s
Honored Contributor

Re: Rename field in only one table

Yeah this is similar let me just recode it quickly...

For vTable = 0 to NoOfTables() -1 

   Let vTableName = TableName(vTable); 

For f = 1 to NoOfFields('$(vTableName)') 

    let vFieldOld = FieldName($(f),'$(vTableName)'); 

    let vFieldNew = Replace('$(vFieldOld)','.',''); 

    RENAME FIELD $(vFieldOld) to $(vFieldNew); 

Next f;  

Next ; 

adamdavi3s
Honored Contributor

Re: Rename field in only one table

I guess this should work but will need to test it

For vTable = 0 to NoOfTables() -1 

   Let vTableName = TableName(vTable); 

For f = 1 to NoOfFields('$(vTableName)') 

    let vFieldOld = FieldName($(f),'$(vTableName)'); 

    let vFieldNew = Replace('$(vFieldOld)','$(vTableName)'&'.',''); 

    RENAME FIELD $(vFieldOld) to $(vFieldNew); 

Next f;  

Next ; 

adamdavi3s
Honored Contributor

Re: Rename field in only one table

Ok so it works except where you have fields with the same name, i.e. linked fields...

give me a mo to see if I can figure that one out!

adamdavi3s
Honored Contributor

Re: Rename field in only one table

Check out Marco's second post on this thread which gets over the issue of not renaming fields the same

Rename fields using mapping table | Qlik Community