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: 
Not applicable

Limit SQL Rows Loaded

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

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

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.

View solution in original post

10 Replies
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

Not applicable
Author

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
Luminary Alumni
Luminary Alumni

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
Creator II
Creator II

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
Specialist III
Specialist III

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

Not applicable
Author

Jagan,

Thank you very much.

Problem solved!

jagan
Luminary Alumni
Luminary Alumni

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.

Not applicable
Author

Done.

Thank you again.