Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. $300 savings extended to February 9th Learn More
amigo007
New Contributor III

Renaming fields based on their values

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:

IDAnswerCityCountry
1GoodMadridSpain
2Very GoodParisFrance
3AverageLondonUK
4AverageParisFrance
5GoodOsloNorway
6SatisfiedHelsinkiFinland
7Not SatisfiedMadridSpain
8GoodRomeItaly

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'):

IdentifierAnswer_Good
AGood
BGood
CGood
DGood

Table 2 (for answers with value 'Average):

ClassAnswer_Average
Class1Average
Class2Average
Class3Average
Calss..nAverage

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.

9 Replies

Re: Renaming fields based on their values

Data:

LOAD * Inline [

Field

a

b

c

d

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;

NEXT

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

antoniotiman
Honored Contributor III

Re: Renaming fields based on their values

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

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

antoniotiman
Honored Contributor III

Re: Renaming fields based on their values

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

effinty2112
Honored Contributor

Re: Renaming fields based on their values

Hi Yassine,

An alternative:

Table:

LOAD * Inline [

Field1

a

b

c

d

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
aa  
b b  
c c  
d d 
e e

Regards

Andrew

amigo007
New Contributor III

Re: Renaming fields based on their values

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?

MVP
MVP

Re: Renaming fields based on their values

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)?

amigo007
New Contributor III

Re: Renaming fields based on their values

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.

Highlighted
MVP
MVP

Re: Renaming fields based on their values

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?