Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Take a look on the generic load statement which is the opposite to a crosstable load.
- Marcus
Take a look on the generic load statement which is the opposite to a crosstable load.
- Marcus
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;
To make it even more dynamic, you can use this For loop:
For i = 1 to FieldValueCount('AttributeName')
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
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
[..]
Great Idea. Actually, this one solved my recent problem.