Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Karl_Hart
Creator
Creator

Dynamically Naming a Field in Load

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?

1 Solution

Accepted Solutions
marcus_sommer

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

The Generic Load

- Marcus

View solution in original post

6 Replies
marcus_sommer

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

The Generic Load

- Marcus

sunny_talwar

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;


Capture.PNG

sunny_talwar

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

For i = 1 to FieldValueCount('AttributeName')

Saravanan_Desingh

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

Saravanan_Desingh

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

[..]

Thiago_Justen_

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

Thiago Justen Teixeira Gonçalves
Farol BI
WhatsApp: 24 98152-1675
Skype: justen.thiago