topic Re: Renaming fields based on their values in QlikView

Renaming fields based on their values

amigo007 — Wed, 25 Nov 2020 16:16:04 GMT

I'm loading a table form a database and there is a field that contains 5 distinct values. Is there a way how to give a different alias for the field based on value, preferably in the loading script? For example let's say that the field is called Field1, if Field1 = 'a' then the alias for the field will be something like Field1_a, etc..

My idea is to join this table to other 5 tables depending on those 5 values. Here is an example with the main table and one table from the other 5 tables:

Main table:

ID	Answer	City	Country
1	Good	Madrid	Spain
2	Very Good	Paris	France
3	Average	London	UK
4	Average	Paris	France
5	Good	Oslo	Norway
6	Satisfied	Helsinki	Finland
7	Not Satisfied	Madrid	Spain
8	Good	Rome	Italy

So the main table has 5 unique values for field 'Aswer'.

So I need yo join the above table with 5 tables depending on the values in 'Answer' field. Here are sample 2 tables:

Table 1 (for answers with value 'Good'):

Identifier	Answer_Good
A	Good
B	Good
C	Good
D	Good

Table 2 (for answers with value 'Average):

Class	Answer_Average
Class1	Average
Class2	Average
Class3	Average
Calss..n	Average

Meaning I need to join the main table to Table 1 where [Table 1].[Answer] = 'Good' and main table to Table 2 where [Table 2].[Answer] = 'Average'

All tables include thousands of rows.

Re: Renaming fields based on their values

Kushal_Chawda — Sun, 24 Sep 2017 09:11:02 GMT

Data:

LOAD * Inline [

Field

e ];

New:

LOAD * Inline [

Temp ];

for i=1 to FieldValueCount('Field')

let vFieldValue = FieldValue('Field',$(i));

Concatenate(New)

LOAD

'if(Field='& chr(39)&'$(vFieldValue)' &chr(39)&',Field,Null()) as Field_$(vFieldValue)' as FieldName

Resident Data;

DROP Field Temp;

concat:

NoConcatenate

LOAD Concat(DISTINCT FieldName,','&chr(10)) as FieldName

Resident New;

DROP Table New;

let vFieldName = Peek('FieldName',0,'concat');

DROP Table concat;

Final:

LOAD *,

$(vFieldName)

Resident Data;

DROP Table Data;

let vFieldName=Null();

Note: Replace inline table and fieldname with your actual table and fieldname

Re: Renaming fields based on their values

antoniotiman — Sun, 24 Sep 2017 09:29:06 GMT

Hi Yassine,

may be like this

Generic
LOAD Field,'Field_'&Field as A,Field as B Inline [
Field
a
b
c
d
e ];

Regards,

Antonio

Re: Renaming fields based on their values

Kushal_Chawda — Sun, 24 Sep 2017 09:37:20 GMT

antoniotiman I think he is using table which could have many fields ,so generic load will not work in that case.

Re: Renaming fields based on their values

antoniotiman — Sun, 24 Sep 2017 09:42:09 GMT

Generic
LOAD Field1,Field,'Field_'&Field as A,Field as B Inline [
Field1,Field
A1,a
A2,b
A3,c
A4,d
A5,e ];

Re: Renaming fields based on their values

effinty2112 — Sun, 24 Sep 2017 11:18:43 GMT

Hi Yassine,

An alternative:

Table:

LOAD * Inline [

Field1

e ];

For i = 1 to FieldValueCount('Field1')

Let vValue = FieldValue('Field1',$(i));

Let vFieldName = 'Field1_' & FieldValue('Field1',$(i));

Left Join (Table)

LOAD

'$(vValue)' as Field1,

'$(vValue)' as $(vFieldName)

Autogenerate(1);

Next i;

Giving

Field1	Field1_a	Field1_b	Field1_c	Field1_d	Field1_e
a	a
b		b
c			c
d				d
e					e

Regards

Andrew

Re: Renaming fields based on their values

amigo007 — Sun, 24 Sep 2017 12:53:15 GMT

Thanks guys for your prompt help.

As I was not sure how to tune your solutions to my input script, I have edited my original post explaining my question with sample tables.

Any help will be highly appreciated?

Re: Renaming fields based on their values

swuehl — Sun, 24 Sep 2017 13:06:25 GMT

What's your expected result (for example, looking at the three sample tables in your original post, how should the resulting joined table look like)?

Re: Renaming fields based on their values

amigo007 — Sun, 24 Sep 2017 13:17:46 GMT

I want to pick from the main tables the needed fields, e.g. 'City' and 'Country', etc..to other 5 tables. So trying to build a data model inside QlikView where I can get selected fields from the main tables to the other tables by joining 'ANswer' field.

Hopefully that clarifies.

Re: Renaming fields based on their values

swuehl — Sun, 24 Sep 2017 13:31:40 GMT

I am not really sure I understand what you are trying to achieve.

What about the solutions proposed so far?

Or just concatenating the 5 tables and renaming the Answer_XXX field in each table to Answer, so the concatenated table is linked by Answer field and your main table keeps unchanged?