topic Re: Dynamically Naming a Field in Load in QlikView

Dynamically Naming a Field in Load

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

Hello all,

I have a table of attributes such as the below

Property AttributeKey AttributeName AttributeAnswer

1 1 Build Date 01/01/2000

1 2 Build Material Brick

1 3 Property Type House

2 1 Build Date 01/01/2001

2 2 Build Material Stone

2 3 Property Type Flat

I want to turn this into the below table in the load script

Property Build Date Build Material Property Type

1 01/01/2000 Brick House

2 01/01/2001 Stone Flat

I currently have it set up to do

AttributeMap1:

Mapping LOAD Property

AttributeAnswer as [Build Date]

Resident AttributeTable

where AttributeKey ='1';

AttributeMap2:

Mapping LOAD Property

AttributeAnswer as [Build Material]

Resident AttributeTable

where AttributeKey ='2';

AttributeMap3:

Mapping LOAD Property

AttributeAnswer as [Property Type]

Resident AttributeTable

where AttributeKey ='3';

Then

Property:

Applymap('AttributeMap1', Property , null()) as [Build Date],

Applymap('AttributeMap2', Property , null()) as [Build Material],

Applymap('AttributeMap3', Property , null()) as [Property Type]

This works, but I have hundreds of Attributes and 20,000 properties and this load is taking far too long, is there a more efficient way of doing it?

Re: Dynamically Naming a Field in Load

marcus_sommer — Tue, 05 Apr 2016 12:43:58 GMT

Take a look on the generic load statement which is the opposite to a crosstable load.

The Generic Load

- Marcus

Re: Dynamically Naming a Field in Load

sunny_talwar — Tue, 05 Apr 2016 12:46:38 GMT

May be this:

Table:

LOAD * Inline [

Property, AttributeKey, AttributeName, AttributeAnswer

1, 1, Build Date, 01/01/2000

1, 2, Build Material, Brick

1, 3, Property Type, House

2, 1, Build Date, 01/01/2001

2, 2, Build Material, Stone

2, 3, Property Type, Flat

];

FinalTable:

LOAD 0 as Dummy

AutoGenerate 1;

For i = 1 to 3

LET FieldValue = '[' & FieldValue('AttributeName', $(i)) & ']';

Join (FinalTable)

LOAD Property,

AttributeAnswer as $(FieldValue)

Resident Table

Where AttributeKey = $(i);

NEXT

DROP Table Table;

DROP Field Dummy;

Re: Dynamically Naming a Field in Load

sunny_talwar — Tue, 05 Apr 2016 12:50:59 GMT

To make it even more dynamic, you can use this For loop:

For i = 1 to FieldValueCount('AttributeName')

Re: Dynamically Naming a Field in Load

Saravanan_Desingh — Thu, 07 Apr 2016 16:55:17 GMT

One more version, as per Marcus_Sommer‌ idea.

Data:
LOAD * Inline [
Property,    AttributeKey, AttributeName,          AttributeAnswer   
1,            1,            Build Date,            01/01/2000   
1,            2,            Build Material,        Brick
1,            3,            Property Type,          House
2,            1,            Build Date,            01/01/2001   
2,            2,            Build Material,        Stone
2,            3,            Property Type,          Flat
];


GenericLabel:
Generic Load  Property,    AttributeName,          AttributeAnswer 
Resident Data;




CombinedGenericTable:
Load Property Resident Data;
 
FOR i = NoOfTables()-1 to 0 STEP -1
  LET vTable=TableName($(i));
  IF WildMatch('$(vTable)', 'GenericLabel.*') THEN
  LEFT JOIN ([CombinedGenericTable]) LOAD * RESIDENT [$(vTable)];
  DROP TABLE [$(vTable)];
  ENDIF
NEXT i

Re: Dynamically Naming a Field in Load

Saravanan_Desingh — Thu, 07 Apr 2016 17:07:02 GMT

About Generic Load (to my knowledge):

Generic tables must contain at least 3 Fields.

Last Field is a Value

Last but-one Field is an Attribute

From Field 1 thru Last but-three, it will be considered as Key or Sync-Key

If you have less than three Fields, try as below:

Generic

LOAD

1, * INLINE

[..]

Re: Dynamically Naming a Field in Load

Thiago_Justen_ — Thu, 11 Jan 2018 20:45:17 GMT

Great Idea. Actually, this one solved my recent problem.