Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good day,
I have been tasked with loading the old script into Qlik and am having some trouble as there are over 10million rows in the subquery.
What i need is to limit the data loaded during testing as my 6GB of RAM is not enough.
My questions are as follows:
1. Looking at the code bellow have you used the correct code / code structure to achieve what is needed.
2. How do i limit the ROW's loaded for testing purposes. I have looked at "FIRST" and "BUFFER" but do not see those helping. What am i missing here. I have tested the code in MySQL Workbench with a load limit and all works fine.
OLD SCRIPT - Some sort of reporting system
pricing
SELECT
`stock_management1`.`st_prodcode`,
`stock_management1`.`st_sdesc`,
`stock_management1`.`st_mstockist`,
`stock_management1`.`APS_rol`,
`stock_management1`.`APS_eoq`,
`stock_management1`.`APS_ms`
FROM `pricing`.`stock_management` `stock_management1`
WHERE (`stock_management1`.`st_mstockist`='BRA'
OR `stock_management1`.`st_mstockist`='FCS'
OR `stock_management1`.`st_mstockist`='FKE')
AND `stock_management1`.`APS_ms`>0
EXTERNAL JOIN
stock_management1.st_prodcode={?APS: ITEM1.ST_Prodcode}
AND stock_management1.st_prodcode={?FCS: stocktake1.S_ProdCode}
AND stock_management1.st_prodcode={?CENTRAL: Command.mv_PRODCODE}
NEW QLIK CODE
PRICING:
buffer SQL SELECT `APS_eoq`,
`APS_ms`,
`APS_rol`,
stock_management.`st_mstockist`,
stock_management.`st_prodcode`,
stock_management.`st_sdesc`
FROM pricing.`stock_management`
Left Join (aps.`item`, cstores.`stocktake`, central.`mvmnt`)
ON pricing.`stock_management`.`st_prodcode` = aps.`item`.`ST_Prodcode`
AND pricing.`stock_management`.`st_prodcode` = cstores.`stocktake`.`s_prodcode`
AND pricing.`stock_management`.`st_prodcode` IN (SELECT MV_PRODCODE
FROM central.`outstanding_stv`
INNER JOIN central.`mvmnt` on central.`mvmnt`.`MV_TRANNO` = central.`outstanding_stv`.`IS_INVNO`
WHERE R_BRANCH="APS")
WHERE `stock_management`.`APS_ms`>0
AND `stock_management`.`st_mstockist`="BRA"
OR `stock_management`.`st_mstockist`="FCS"
OR `stock_management`.`st_mstockist`="FKE";
I am fairly new to Qlik and any help would be greatly appreciated.
Thank you
Hi,
Try like in your dev machine
SELECT *
FROM table_name
LIMIT 1000;
For loading first 1000 rows.
Note: while migrating to production remove LIMIT 1000.
Hope this helps you.
Regards,
Jagan.
Hi Mark,
BUFFER and FIRST are LOAD statements, not SQL statements. Therefore, you should use them (and they are supposed to work like)
FIRST 100 LOAD *;
SQL SELECT *
FROM Customers;
What happens in the code above? The SQL is executed first, and will pull from the database all the rows from table Customers. However, QlikView will only put into memory the first 100 rows loaded, then it will drop the rest.
There is no rule of thumb but to sample and test, as you know, a "row" as such can be as big as several kilobytes or as small as a byte. But if you know that, and having read the following:
The Importance Of Being Distinct
Symbol Tables and Bit-Stuffed Pointers
That could help.
Miguel
Hi Miguel,
Thanks for the feedback and clearing up the difference between LOAD statement and SQL.
The code above when run end ups with "client out of memory" error.
From your explanation I now understand that i need to limit the load within the SQL code rather than through a Qlik statement.
Thanks again.
Hi,
Try like in your dev machine
SELECT *
FROM table_name
LIMIT 1000;
For loading first 1000 rows.
Note: while migrating to production remove LIMIT 1000.
Hope this helps you.
Regards,
Jagan.
Instead of hitting on the run button to load the data
go to the script editor
click on the Debug button
select limit load and enter the number of records you want to load
Hi,
In order to limit the load we can use:
1. Where condition based on one primary key/field from the table.
2. Debug mode, we can limit records: 20000(eg)
and run the task so that only limited number of records will be loaded
If you are using SQL server as your database you can try like this
PRICING:
SQL SELECT TOP 1000 `APS_eoq`,
`APS_ms`,
`APS_rol`,
stock_management.`st_mstockist`,
stock_management.`st_prodcode`,
stock_management.`st_sdesc`
FROM pricing.`stock_management`
Left Join (aps.`item`, cstores.`stocktake`, central.`mvmnt`)
ON pricing.`stock_management`.`st_prodcode` = aps.`item`.`ST_Prodcode`
AND pricing.`stock_management`.`st_prodcode` = cstores.`stocktake`.`s_prodcode`
AND pricing.`stock_management`.`st_prodcode` IN (SELECT MV_PRODCODE
FROM central.`outstanding_stv`
INNER JOIN central.`mvmnt` on central.`mvmnt`.`MV_TRANNO` = central.`outstanding_stv`.`IS_INVNO`
WHERE R_BRANCH="APS")
WHERE `stock_management`.`APS_ms`>0
AND `stock_management`.`st_mstockist`="BRA"
OR `stock_management`.`st_mstockist`="FCS"
OR `stock_management`.`st_mstockist`="FKE";
Jagan,
Thank you very much.
Problem solved!
Hi Mark,
If you got the answer please close this thread by giving Correct Answer to the post which helps you in finding the answer. This helps others in identifying the correct answers easily.
Regards,
jagan.
Done.
Thank you again.