Limit SQL Rows Loaded

[#1]

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

[Miguel_Angel_Baeyens]

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

[#3]

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.

[jagan · Accepted solution]

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.

[svenkita]

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

[vardhancse]

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

[Kushal_Chawda]

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";

[#8]

Jagan,

Thank you very much.

Problem solved!

[jagan]

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.

[#10]

Done.

Thank you again.