Dynamically Naming a Field in Load

[Karl_Hart]

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?

[marcus_sommer · Accepted solution]

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;

[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.