Any alternate to Generic load?

[chaudhariv]

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

[sunny_talwar]

You can use for loop also. See one example here -> Re: Dynamically Naming a Field in Load

[petter]

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

[chaudhariv]

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.

[MarcoWedel]

maybe there's no need to recombine your tables created by the generic load:

The Generic Load

hope this helps

regards

Marco

[chaudhariv]

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.

[chaudhariv]

You are right, Until I am joining these tables it works fine.

But I need the resultant table which further links to other tables.

[sunny_talwar]

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

[#9]

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.

[petter]

Yes ... this is in line with what I would suggest too. This should quite quick and efficient too.