topic Re: Limit SQL Rows Loaded in QlikView

Limit SQL Rows Loaded

Wed, 17 Jun 2015 06:08:34 GMT

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

Re: Limit SQL Rows Loaded

Miguel_Angel_Baeyens — Wed, 17 Jun 2015 12:47:56 GMT

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

Re: Limit SQL Rows Loaded

Thu, 18 Jun 2015 06:17:38 GMT

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.

Re: Limit SQL Rows Loaded

jagan — Thu, 18 Jun 2015 06:22:33 GMT

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.

Re: Limit SQL Rows Loaded

svenkita — Thu, 18 Jun 2015 06:25:00 GMT

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

Re: Limit SQL Rows Loaded

vardhancse — Thu, 18 Jun 2015 06:30:57 GMT

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

Re: Limit SQL Rows Loaded

Kushal_Chawda — Thu, 18 Jun 2015 06:50:21 GMT

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

Re: Limit SQL Rows Loaded

Thu, 18 Jun 2015 07:10:46 GMT

Jagan,

Thank you very much.

Problem solved!

Re: Limit SQL Rows Loaded

jagan — Thu, 18 Jun 2015 07:22:58 GMT

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.

Re: Limit SQL Rows Loaded

Thu, 18 Jun 2015 08:41:32 GMT

Done.

Thank you again.

Re: Limit SQL Rows Loaded

AlbaJPDupe — Thu, 25 May 2023 10:05:49 GMT

Dear all,

I tried this code and it works for me:

Table:

Load *

;

First 1000

SQL Select

From table_name;

Hope it helps 🙂

Regards,

Alba