Chunk data returned from a query

[#1]

Hi,

I have a very simple query in my load script that is pulling a lot of rows (300+ million) from a table in Oracle. It was working fine for a while (so there's nothing wrong with the query's syntax), but it started timing out recently. I've spoken to my DBA, and he said that I need to chunk my data so that I'm not going after all the rows at one time.

Here's the original query:

PartnerTerritoryOrderHistory:

LOAD

    [MI Country KY],

    [Partner ID],

    [MI Product Key],

    [Action Type],

    [Status Type]

;

SQL

SELECT

    hst.mi_country_ky AS "MI Country KY",

    hst.partner_ky AS "Partner ID",

    hst.mi_product_ky AS "MI Product Key",

    hst.latest_ofr_action_type AS "Action Type",

    hst.latest_ofr_status AS "Status Type"

FROM

    rpt_dw.dcr_order_history_product hst

WHERE

    hst.PARTNER_KY IN (183,13245,1733,1089,28209,25683,28449,27969,27611,26487,25565,25563,25005,23881,23761,27649,26847,26567,25885,24563,24443,3103,546,27651,27327,26767,26003,25883,25083,8337,23921,27249,25443,24843,24765,24763,24723,24403,24203,27489,26967,26647,26609,26607,26485,26045,25643,25363,25283,25123,1433,22803,21319,21081,20685,20519,12631,22125,21921,21483,21441,21159,16199,2111,16519,5257,2879,23321,21721,21521,1331,16001,19281,2899,22721,7579,21199,2705,6497,20041,6657,23521,2237,10657,22601,22561,21601,2695,20279,18959,22481,21641,20959,20319,3701,15441,14603,21279,21121,21039,15839,12227,2416,2839,6777,22321,21361,20799,20559,13801,20399,14919,20199,20159,18919,18439,17399,16999,16039,15999,13799,18319,17439,16479,16159,13679,18489,18279,17279,15279,14363,18079,14999,18359,17999,17519,17239,16439,14843,14361,14359,13681,13605,15759,15639,15439,4737,11027,13959,18533,13167,13247,12999,11819,12143,11541,11381,2709,2699,2691,10699,2823,8581,8819,9137,2552,10577,9819,8699,7777,3463,3977,3337,10021,2707,4057,7977,6737,8977,6857,3617,2829,2847,3079,7711,5459,2254,2236,4937,10937,7057,7337,7177,3741,1894,1893,11021,1714,1655,1614,4217,1151,368,277,8457,4897,3857,7539,648,3073,3067,9737,10457,192,9459,9423,2821);

STORE * FROM PartnerTerritoryOrderHistory INTO '..\qvds\PartnerTerritoryOrderHistory.qvd' (QVD);

DROP TABLES PartnerTerritoryOrderHistory;

In the end, I want the result to be the same (i.e., all the data written to a single QVD). What I want to do going forward is have the load script automatically query this table by every unique country key (mi_country_ky column). In other words, let's say I have 200 unique country keys under the mi_country_ky column. I would want the script to loop through and essentially perform 200 individual queries by unique country key (thus "chunking" my results in 200 separate buckets), but still write all the data from each of the 200 queries to the same QVD (PartnerTerritoryOrderHistory.qvd). I don't know how to modify my script to do this though. Can anyone provide the correct syntax for this desired "looping/concatenation" that I want?

Thanks,

Kenny

[krishna_2644]

PartnerTerritoryOrderHistory:

LOAD

    [MI Country KY],

    [Partner ID],

    [MI Product Key],

    [Action Type],

    [Status Type]

;

SQL

SELECT

    hst.mi_country_ky AS "MI Country KY",

    hst.partner_ky AS "Partner ID",

    hst.mi_product_ky AS "MI Product Key",

    hst.latest_ofr_action_type AS "Action Type",

    hst.latest_ofr_status AS "Status Type"

FROM

    rpt_dw.dcr_order_history_product hst

WHERE

    hst.PARTNER_KY = '183';

'// Comment - You can use 'Concatenate' keyword on top of every load statement,but here in this case all the Fields  from the queries are same hence QV Appends the rows from each query,at the bottom of the table generated from the  Topmost load script//

//concatenate - refer to the comment//

LOAD

    [MI Country KY],

    [Partner ID],

    [MI Product Key],

    [Action Type],

    [Status Type]

;

SQL

SELECT

    hst.mi_country_ky AS "MI Country KY",

    hst.partner_ky AS "Partner ID",

    hst.mi_product_ky AS "MI Product Key",

    hst.latest_ofr_action_type AS "Action Type",

    hst.latest_ofr_status AS "Status Type"

FROM

    rpt_dw.dcr_order_history_product hst

WHERE

    hst.PARTNER_KY ='13245' ;

- - -

- - -

- - -

STORE * FROM PartnerTerritoryOrderHistory INTO '..\qvds\PartnerTerritoryOrderHistory.qvd' (QVD);

DROP TABLES PartnerTerritoryOrderHistory;

May be this way.

But this is too tedious.

[Clever_Anjos]

Wondering why you don´t have a date field into your rpt_dw.dcr_order_history_product , the usual would be having a date field and use it as splitter (as Incremental Load from your qlikview manual)

[#4]

Hi Clever...thanks for your reply. I'm assuming you're talking about putting something in so that I only load the delta of what's changed since the previous load (FYI - I only run this once every 24hrs)? I'm just a beginner so I'm afraid I don't know how to do that. Isn't there some way I could modify the script to just loop through every country key (I want to do on country key rather than partner key) and just concatenate all the results at the end? Thanks again.

[Clever_Anjos]

country key is a fixed list?

[#6]

Well, it's more of a fixed list than the partner key list. In other words, it would be very rare for a new country key to be added to that column, but I suppose it's possible. Ideally, I'd like to write the script so that it loops through each unique country key under that column -- whatever that list of country keys might be at any given moment.

[Clever_Anjos · Accepted solution]

I think something like this should work

Country:
LOAD * Inline [
Country
India
China
USA
];


For i = 1 to NoOfRows('Country');
  LET country = Peek('Country',i,'Country');
  PartnerTerritoryOrderHistory:
  LOAD
     [MI Country KY],
     [Partner ID],
     [MI Product Key],
     [Action Type],
     [Status Type]
  ;
  SQL
  SELECT
     hst.mi_country_ky AS "MI Country KY",
     hst.partner_ky AS "Partner ID",
     hst.mi_product_ky AS "MI Product Key",
     hst.latest_ofr_action_type AS "Action Type",
     hst.latest_ofr_status AS "Status Type"
  FROM
     rpt_dw.dcr_order_history_product hst
  WHERE
  hst.coyntry = '$(country)';
Next

[#8]

What does line 31 with "Next" do? What would I write after "Next?" Would it be the Store statement for the QVD? Apologies, but what you wrote above is a little advanced for me, so I'm not sure what the code is doing.

[Clever_Anjos]

Next indicates that qlikview need to execute next "for" loop increment

After next you can store your qvd, yes

[Clever_Anjos]

Further reading http://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/ScriptControlStatements...

Incremental load (if you can use a date field)

http://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/QVD_Incremental.htm