Rename 'fields name' to 'include table' name

[#1]

Is there a tip to quickly rename all fields of a table as follows:

[tablename fieldname] leading to 'Account Id' instead of 'Id' when loading the Id field from Account.

Writing a load Id as [Account Id] statement for each fields is a lot of work...

Thanks for your answer !

[#2 · Accepted solution]

Will Qualify stmt work for you?

That is the easiest option to use.

[rwunderlich]

I agree with Vidyut that qualify is the easiest option. However, if that won't work for you, remember that RENAME FIELDS can use a Map. You could build the map dynaimcally with a sub like this:

SUB renameFields (table, prefix)
 FOR i = 1 to NoOfFields('$(table)')
 LET fld = FieldName($(i), '$(table)');
 mapTemp:
 LOAD '$(fld)' as RenameFrom, '$(prefix) ' & '$(fld)' as RenameTo
 RESIDENT [$(table)];
 NEXT i
 RenameMap:
 MAPPING LOAD * RESIDENT mapTemp;
 DROP TABLE mapTemp;
 RENAME FIELDS USING RenameMap;
END SUB
call renameFields('data', 'Customer') // Add "Customer " all fieldnames in table "data"

-Rob

[#4]

Thanks QUALIFY works perfect.

now, How do I unqualify one field to allow linking it to a field in another table whie renaming it as the same time:

do not really understand how QUNQUALIFY applies (old field name or renamed filed name ?

[mongolu]

you can't, by the means of UNQUALIFY.

for an already loaded field you can only do a 'RENAME FIELD [FIELD1] TO [FIELD2]', only if the [FIELD2] doesn't match (by name) another loaded field.

if you are in the "only" scenario, you can bypass this by making a self-join to the table containing FIELD1. suppose you have this table loaded with the "QUALIFY *":

QUALIFY *;

T1:

LOAD * INLINE [

ID,FIELD1,FIELD2

1,TEXT1,TEXT11

2,TEXT2,TEXT22

3,TEXT1,TEXT11];

UNQUALIFY *;

you can do the self-join like that:

INNER JOIN (T1)

LOAD DISTINCT T1.FIELD2, T1.FIELD2 AS FIELD2

RESIDENT T1;

the problem is with a big self-joining table... RESIDENT works slower than an optimized-load from a .QVD file.

[rwunderlich]

jeffrobertz wrote:
How do I unqualify one field to allow linking it to a field in another table whie renaming it as the same time:
do not really understand how QUNQUALIFY applies (old field name or renamed filed name ?

If you want to unqualify one or more fields to use for linking, do it like this -- before loading.

QUALIFY *; // Qualify all fields
UNQUALIFY Id; // Except for the Id field
LOAD ....

The UNQUALIFIED field can be a new name created by an "as" clause.. You will specify the renamed field. For example:

QUALIFY *;
UNQUALIFY LinkId;
TabA:
LOAD *, Id as LinkId;
LOAD * INLINE [
Id, Customer, Amount
1, ABC, 200
];

This will generate a table with fields named TabA.Id, TabA.Customer, TabA.Amount and LinkId.

-Rob

[#7]

Hi...

my source is an excel file...

how to include the table name.

excel file name is join.xls

thanks

bharathe

[#8]

Hi Bharathe

the table name is given by the label before the load statement, not by the excel file or sheet.

to add the label to your fields use the QUALIFY *; before the load.

I prefer to make frequent use of QUALIFY, UNQUALIFY as the setting remains and reordering or adding of loads might give unwanted results.

The QUALIFY/UNQUALIFY statements allow also for wildcards, you could e.g. state

UNQUALIFY '*_ID';

Regards

Juerg