topic Rename 'fields name' to 'include table' name in QlikView

Rename 'fields name' to 'include table' name

Fri, 15 May 2009 23:17:55 GMT

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 !

Rename 'fields name' to 'include table' name

Fri, 15 May 2009 23:35:46 GMT

Will Qualify stmt work for you?

That is the easiest option to use.

Rename 'fields name' to 'include table' name

rwunderlich — Sat, 16 May 2009 05:01:57 GMT

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

Rename 'fields name' to 'include table' name

Sun, 17 May 2009 00:08:32 GMT

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 ?

Rename 'fields name' to 'include table' name

mongolu — Sun, 17 May 2009 02:58:09 GMT

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.

Rename 'fields name' to 'include table' name

rwunderlich — Sat, 23 May 2009 06:18:48 GMT


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

Rename 'fields name' to 'include table' name

Tue, 11 May 2010 13:00:53 GMT

Hi...

my source is an excel file...

how to include the table name.

excel file name is join.xls

thanks

bharathe

Rename 'fields name' to 'include table' name

Tue, 11 May 2010 14:07:06 GMT

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