topic Re: Any alternate to Generic load? in QlikView

Any alternate to Generic load?

chaudhariv — Tue, 12 Apr 2016 19:46:13 GMT

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

Re: Any alternate to Generic load?

sunny_talwar — Tue, 12 Apr 2016 20:19:50 GMT

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

Re: Any alternate to Generic load?

petter — Tue, 12 Apr 2016 20:35:44 GMT

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

Re: Any alternate to Generic load?

chaudhariv — Tue, 12 Apr 2016 20:39:32 GMT

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.

Re: Any alternate to Generic load?

MarcoWedel — Tue, 12 Apr 2016 20:40:40 GMT

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

The Generic Load

hope this helps

regards

Marco

Re: Any alternate to Generic load?

chaudhariv — Tue, 12 Apr 2016 20:45:35 GMT

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.

Re: Any alternate to Generic load?

chaudhariv — Tue, 12 Apr 2016 20:53:04 GMT

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

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

Re: Any alternate to Generic load?

sunny_talwar — Wed, 13 Apr 2016 00:24:45 GMT

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

Re: Any alternate to Generic load?

Wed, 13 Apr 2016 01:12:32 GMT

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.

Re: Any alternate to Generic load?

petter — Wed, 13 Apr 2016 09:37:20 GMT

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

Re: Any alternate to Generic load?

Colin-Albert — Wed, 13 Apr 2016 09:41:08 GMT

As Marco suggested, marcowedel‌ you do not need to join the tables created by the Generic load.

It is the join that is causing the slow load not the generic table processing.

Re: Any alternate to Generic load?

marcus_sommer — Wed, 13 Apr 2016 10:38:06 GMT

Have you considered any alternative approach to the transforming of your table into thousands of columns? I couldn't imagine that this is really practically.

- Marcus

Re: Any alternate to Generic load?

sunny_talwar — Wed, 13 Apr 2016 10:48:56 GMT

Petter‌ I think one potential issue is that we would need a if statement for each of the new column we are trying to build. Is that true? I feel what if the columns change dynamically where new things get added and old get removed, this might get a little difficult to maintain.

What is your opinion on this approach -> Re: Any alternate to Generic load? Is this going to be slower compared to the Group By approach?

Also, as a separate question, we have seen some very poor performance of Group By statement on the server at my company. Not sure what the reason behind poor performance on Server is, but assuming everything else constant are loops better than Group By or Group by is better than loops? -> This is for my personal knowledge as I am working on getting rid of Group By statements and replacing them with For Loops to improve performance.

Re: Any alternate to Generic load?

marcus_sommer — Wed, 13 Apr 2016 11:27:40 GMT

I'm not sure but I feel everything would be faster then a group by load ... whereby I haven't used it in a usecase like this. Are there really performance issues (with group by or a loop) I would think if it's possible to create it with loops on the field-values - I mean to run through internal tables.

Beside them I would check if I really need these process steps to get the end-results which I need (see also my comment from above).

- Marcus

Re: Any alternate to Generic load?

chaudhariv — Wed, 13 Apr 2016 12:46:59 GMT

I have no idea about any alternate approach.

Re: Any alternate to Generic load?

chaudhariv — Wed, 13 Apr 2016 12:49:08 GMT

I think the Inline would work if I have less number of Values that needs to be converted to columns, but in my case I have thousands of Values.

Re: Any alternate to Generic load?

Anonymous — Thu, 21 Jun 2018 05:27:40 GMT

How do I get the columns to row in below example

Source Table

KeyID ColumnID ColumnValue

1235 ProductName ABCD Product

1235 QTY 50

1235 Status Active

1235 ModDate 06/05/2018

1236 ProductName AAAA Product

1236 QTY 70

1236 Status Inactive

1236 Moddate 08/05/2018

1240 ProductName BBB Product

1240 QTY 67

1240 Status Active

1240 Moddate 10/05/2018

Result table !

KeyID ProductName QTY Status ModDate

1235 ABCD Product 50 Active 06/05/2018

1236 AAAA Product 70 Inactive 08/05/2018

1240 BBB Product 67 Active 10/05/2018 !

Any help would be appreciated

I tried with generic load however it's duplicate the rows. Therefore looking for deferent method

Re: Any alternate to Generic load?

imsushantjain — Mon, 07 Jan 2019 10:47:13 GMT

Hi Sunny,

I have data that exist in this particular format :

RISK_EXPDEF_NAME	FACILITY_NUMBER	INTERNAL_BUNIT	INTERNAL_LENTITY	T_FIN_STRATEGY	T_PLL_YEAR	TOTAL_LIMIT
Cumulative Loss	FOSP	FO - BU	CL - LE	System Trading		-1000000
Cumulative Loss	FOSP	FO - BU	CL - LE	Third Party Trading		-1000000
Cumulative Loss	FOSP	SP - BU	CL - LE	System Trading		-1000000
Cumulative Loss	FOSP	SP - BU	CL - LE	Third Party Trading		-1000000
VaR	FOSP	FO - BU	CL - LE	System Trading		1000000
VaR	FOSP	FO - BU	CL - LE	Third Party Trading		1000000
VaR	FOSP	SP - BU	CL - LE	System Trading		1000000
VaR	FOSP	SP - BU	CL - LE	Third Party Trading		1000000

I want to move RISK_EXPDEF_NAME values to column and list the values TOTAL_LIMIT under it, e.g.

FACILITY_NUMBER	INTERNAL_BUNIT	INTERNAL_LENTITY	T_FIN_STRATEGY	T_PLL_YEAR	Cumulative Loss	VaR
FOSP	FO - BU	CL - LE	System Trading		100000	-100000
FOSP	FO - BU	CL - LE	Third Party Trading		100000	-100000
FOSP	SP - BU	CL - LE	System Trading		100000	-100000
FOSP	SP - BU	CL - LE	Third Party Trading		100000	-100000

How to achieve this particular case with above mentioned script?

Regards

Sushant

Re: Any alternate to Generic load?

sunny_talwar — Mon, 07 Jan 2019 13:00:00 GMT

Here you go

Table:
LOAD * INLINE [
    RISK_EXPDEF_NAME, FACILITY_NUMBER, INTERNAL_BUNIT, INTERNAL_LENTITY, T_FIN_STRATEGY, T_PLL_YEAR, TOTAL_LIMIT
    Cumulative Loss, FOSP, FO - BU, CL - LE, System Trading,  , -1000000
    Cumulative Loss, FOSP, FO - BU, CL - LE, Third Party Trading,  , -1000000
    Cumulative Loss, FOSP, SP - BU, CL - LE, System Trading,  , -1000000
    Cumulative Loss, FOSP, SP - BU, CL - LE, Third Party Trading,  , -1000000
    VaR, FOSP, FO - BU, CL - LE, System Trading,  , 1000000
    VaR, FOSP, FO - BU, CL - LE, Third Party Trading,  , 1000000
    VaR, FOSP, SP - BU, CL - LE, System Trading,  , 1000000
    VaR, FOSP, SP - BU, CL - LE, Third Party Trading,  , 1000000
];

FinalTable:
LOAD DISTINCT FACILITY_NUMBER,
	 INTERNAL_BUNIT,
	 INTERNAL_LENTITY,
	 T_FIN_STRATEGY,
	 T_PLL_YEAR
Resident Table;

FOR i = 1 to FieldValueCount('RISK_EXPDEF_NAME')

	 LET FieldValue = FieldValue('RISK_EXPDEF_NAME', $(i));
	 
	 Left Join (FinalTable)
	 LOAD FACILITY_NUMBER,
	 	  INTERNAL_BUNIT,
		  INTERNAL_LENTITY,
		  T_FIN_STRATEGY,
	 	  T_PLL_YEAR,
	 	  TOTAL_LIMIT as [$(FieldValue)]
	 Resident Table
	 Where RISK_EXPDEF_NAME = '$(FieldValue)';
	 
NEXT

DROP Table Table;

Re: Any alternate to Generic load?

imsushantjain — Tue, 08 Jan 2019 01:47:47 GMT

Thanks a lot Sunny, Its working well.

My Request : If you can add comments (to your original code ) if will help a lot of users to understand the logic quickly .