Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be check this out:
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.
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
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
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.
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 ;
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 ;
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!
Check out Marco's second post on this thread which gets over the issue of not renaming fields the same