Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

w0jt4z
New Contributor II

Optimization loading data, too much data then error

Hi, 
i can't solve my problem in the other way.
One of table in my database is very large, but I need only 3 columns of there. In simplification it is OrderKey(required), DataTypeId and Value. 
I need to show it on the table, order and value of each data types in separate columns. I did it on the dashboard, but it's very suboptimal solution. 
I create new column, then:
If (DataTypeId = 1, Value, null()), and i set the label as for example "City"
Next column:
If (DataTypeId =2, Value, null(), the label as for example "Name"
et cetera, et cetera. 
But with more columns (There is about 40 data types Id) I have error (to little memory). 
I want to do it on the load script, but i don't know how. 
I want to create field for example "City" and there load only DataTypeId =1. 
Then on my table i want to add order and columns "City", "Name", et cetera from ready fields. 

Thanks a lot for any solutions. 

Best Regards. 

1 Solution

Accepted Solutions
anushree1
Valued Contributor

Re: Optimization loading data, too much data then error

Please check the attached file

9 Replies
w0jt4z
New Contributor II

Re: Optimization loading data, too much data then error

No one can help me?
I tried to find more information in internet, i found something about crosstable, but i don't know how to use it and will it help me in my problem.

anushree1
Valued Contributor

Re: Optimization loading data, too much data then error

Could you share the sample input and desired output 

w0jt4z
New Contributor II

Re: Optimization loading data, too much data then error

Somenthing like that.

anushree1
Valued Contributor

Re: Optimization loading data, too much data then error

Please check the attached file

w0jt4z
New Contributor II

Re: Optimization loading data, too much data then error

It works! Thank You very much! 
Additional question: 
I don't understand this part of script:

[TMP1]:
GENERIC LOAD * RESIDENT [Data];
[RESULT]:

LOAD DISTINCT BookingItemOrderItemKey RESIDENT [Data];

DROP TABLE [Data];
FOR i = 0 to NoOfTables()



TableList:

LOAD TableName($(i)) AS Tablename AUTOGENERATE 1

WHERE WildMatch(TableName($(i)), 'TMP1.*');

NEXT i
FOR i = 1 to FieldValueCount('Tablename')

LET vTable = FieldValue('Tablename', $(i));

LEFT JOIN ([RESULT]) LOAD * RESIDENT $(vTable);

On the last line I have little problem with the names of mapped values when there having space-key, i have to change it. It may make a problem, because I will try to load the values from other table to the mapping load.  

Could you explain me in simplification what happens in this part of Your script? I don't want just copy and paste script, I would to understand a bit of it. 

Thanks a lot for Your help 🙂

austin478
New Contributor

Re: Optimization loading data, too much data then error

This problem exists for all kinds of applications, however for OLTP. Normally MySQL is rather fast loading data in MyISAM table Prepaid Gift Balance

anushree1
Valued Contributor

Re: Optimization loading data, too much data then error

sure please read through the link https://community.qlik.com/t5/Qlik-Design-Blog/The-Generic-Load/ba-p/1473470

it will help you understand the idea behind the code written , this is like an inverse of cross table

anushree1
Valued Contributor

Re: Optimization loading data, too much data then error

Incase the suggestion has helped you find the solution please mark my response as an accepted solution, so that it helps others also seeking help on similar queries

w0jt4z
New Contributor II

Re: Optimization loading data, too much data then error

Thanks a lot again! 🙂
Best regards,
Wojciech