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: 
chaudhariv
Partner - Contributor III
Partner - Contributor III

Any alternate to Generic load?

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

21 Replies
sunny_talwar

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

petter
Partner - Champion III
Partner - Champion III

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
Partner - Contributor III
Partner - Contributor III
Author

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
Partner - Contributor III
Partner - Contributor III
Author

My data looks like this:

Data.PNG  


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
Partner - Contributor III
Partner - Contributor III
Author

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

Not applicable

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
Partner - Champion III
Partner - Champion III

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