Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sspe
Creator II
Creator 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
sunny_talwar

May be check this out:

How to Rename Fields

jpenuliar
Partner - Specialist III
Partner - Specialist III

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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
Master
Master

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
Master
Master

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
Master
Master

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
Master
Master

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
Master
Master

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