Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Generic load works perfect when we have few values in rows that need to be converted into columns.
But I have column which has thousands of rows that need to be converted to columns.
Any suggestion or work around???
Currently I am using below code, but it is taking hours to complete.
Set vListOfTables = ;
For vTableNo = 0 to NoOfTables()
Let vTableName = TableName($(vTableNo)) ;
If Subfield(vTableName,'.',1)='GenericLabel' Then
Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;
End If
Next vTableNo
CombinedGenericTable:
Load distinct Key From GenericDB;
For each vTableName in $(vListOfTables)
Left Join (CombinedGenericTable) Load * Resident [$(vTableName)];
Drop Table [$(vTableName)];
Next vTableName
You can use for loop also. See one example here -> Re: Dynamically Naming a Field in Load
There is a technique for pivoting with the help of GROUP BY which is very efficient. But before I spend time explaining it I want to make sure that it will be relevant for your case. So could you please give an example of how your input table/tables will look like and how you want the end result to be? It is hard for me to imagine that with the code you provided...
I have 3 fields
Key, Names and Value
How do i loop in these fields.
please elaborate a bit more.
Thanks a ton for your reply.
maybe there's no need to recombine your tables created by the generic load:
hope this helps
regards
Marco
My data looks like this:
And I am able to load it successfully for less number of records with the code that I mentioned.
But this fails for larger set of data.
You are right, Until I am joining these tables it works fine.
But I need the resultant table which further links to other tables.
I think you did not look at the link I attached, but here is the script used in that thread:
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 FieldValueCount('AttributeName')
LET FieldValue = '[' & FieldValue('AttributeName', $(i)) & ']';
Join (FinalTable)
LOAD Property,
AttributeAnswer as $(FieldValue)
Resident Table
Where AttributeKey = $(i);
NEXT
DROP Table Table;
DROP Field Dummy;
You have one less column to worry about in your case, but the idea of the loop is in red above. If you can share your data in an Excel file, we might be able to program it for you
Hi, you can use GROUP BY method. Please find the below example:
Table:
LOAD
Property,
Max(IF(AttributeKey=1,AttributeName)) AS Build_Date,
MaxString(IF(AttributeKey=2,AttributeName)) AS Build_Material,
MaxString(IF(AttributeKey=3,AttributeName)) AS Property_Type
Group By
Property
;
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
];
It worked fine for me on large data set.
Yes ... this is in line with what I would suggest too. This should quite quick and efficient too.