Re: Incremental Reload Issue - Page 2 - Qlik Community

Report Inappropriate Content · ‎2016-08-24

//Initial Load to store DB records(Jan2010-Feb2016) in QVD

TradeTBL:
LOAD buyerbrokercompanyname,
Buyeruderid,
instrument,
Orderid,
Price,
Product,
quantity,
sellerbrokercompanyname,
Selleruserid,
Status,
Strip_Name,
Trade_Date,
units,
iscancelled,
isfromeditor,
isfrombroker,
windowid,
windowname,
isclear,
Hubname,
sellercompanyname,
Seller,
Buyer;

SELECT
o122616.buyerbrokercompanyname AS buyerbrokercompanyname,
o122616.buyeruserid AS Buyeruserid,
o122612.instrument AS instrument,
o122616.orderid AS Orderid,
o122616.price AS Price,
o122610.productname as Product,
o122616.quantity AS quantity,
o122616.sellerbrokercompanyname AS sellerbrokercompanyname,
o122616.selleruserid AS Selleruserid,
o122616.status AS Status,
o122611.stripname AS Strip_Name,
o122616.trade_date AS Trade_Date,
o122616.units AS units,
o122616.iscancelled AS iscancelled,
o122616.isfrombroker AS isfrombroker,
o122616.isfromeditor AS isfromeditor,
o130889.windowid AS windowid,
o130889.windowname AS windowname,
o122616.isclear AS isclear,
o196398.hubname as Hubname,
o122616.sellercompanyname
|| (CASE WHEN o122616.ordertype = 'Offer' THEN '*' ELSE NULL END)
AS Seller,
o122616.buyercompanyname
|| (CASE WHEN o122616.ordertype = 'Bid' THEN '*' ELSE NULL END)
AS Buyer

FROM ewindow.d_product o122610,
ewindow.d_hub o196398,
ewindow.d_strip o122611,
ewindow.d_window_state o122612,
ewindow.t_window o130889,
ewindow.f_transaction o122616

WHERE (o122610.productid = o122616.productid)
AND (o130889.windowid = o122612.windowid)
AND (o122611.stripid = o122616.stripid)
AND (o196398.hubid = o122616.hubid)
AND (o122612.window_state_id = o122616.window_state_id)
AND (o122616.reforderid IS NOT NULL)
AND (o122616.status = 'consummated')
AND (o122616.trade_date >='01-Jan-2010')
AND (o122616.trade_date <='30-Feb-2016')
ORDER BY o122616.trade_date ASC , o122610.productname ASC;

STORE TradeTBL into 'C:\Qlikview\Incremental_Load1.qvd';

// Extract only records from Mar2016 in DB and store in QVD

TradeTBL:
LOAD buyerbrokercompanyname,
Buyeruderid,
instrument,
Orderid,
Price,
Product,
quantity,
sellerbrokercompanyname,
Selleruserid,
Status,
Strip_Name,
Trade_Date,
units,
iscancelled,
isfromeditor,
isfrombroker,
windowid,
windowname,
isclear,
Hubname,
sellercompanyname,
Seller,
Buyer;

SELECT
o122616.buyerbrokercompanyname AS buyerbrokercompanyname,
o122616.buyeruserid AS Buyeruserid,
o122612.instrument AS instrument,
o122616.orderid AS Orderid,
o122616.price AS Price,
o122610.productname as Product,
o122616.quantity AS quantity,
o122616.sellerbrokercompanyname AS sellerbrokercompanyname,
o122616.selleruserid AS Selleruserid,
o122616.status AS Status,
o122611.stripname AS Strip_Name,
o122616.trade_date AS Trade_Date,
o122616.units AS units,
o122616.iscancelled AS iscancelled,
o122616.isfrombroker AS isfrombroker,
o122616.isfromeditor AS isfromeditor,
o130889.windowid AS windowid,
o130889.windowname AS windowname,
o122616.isclear AS isclear,
o196398.hubname as Hubname,
o122616.sellercompanyname
|| (CASE WHEN o122616.ordertype = 'Offer' THEN '*' ELSE NULL END)
AS Seller,
o122616.buyercompanyname
|| (CASE WHEN o122616.ordertype = 'Bid' THEN '*' ELSE NULL END)
AS Buyer

FROM ewindow.d_product o122610,
ewindow.d_hub o196398,
ewindow.d_strip o122611,
ewindow.d_window_state o122612,
ewindow.t_window o130889,
ewindow.f_transaction o122616

WHERE (o122610.productid = o122616.productid)
AND (o130889.windowid = o122612.windowid)
AND (o122611.stripid = o122616.stripid)
AND (o196398.hubid = o122616.hubid)
AND (o122612.window_state_id = o122616.window_state_id)
AND (o122616.reforderid IS NOT NULL)
AND (o122616.status = 'consummated')
AND (o122616.trade_date >='01-Mar-2016')
ORDER BY o122616.trade_date ASC , o122610.productname ASC;

Concatenate
LOAD buyerbrokercompanyname,
Buyeruderid,
instrument,
Orderid,
Price,
Product,
quantity,
sellerbrokercompanyname,
Selleruserid,
Status,
Strip_Name,
Trade_Date,
units,
iscancelled,
isfromeditor,
isfrombroker,
windowid,
windowname,
isclear,
Hubname,
sellercompanyname,
Seller,
Buyer
from 'C:\Qlikview\Incremental_Load1.qvd';

STORE TradeTBL into 'C:\Qlikview\Incremental_Load1.qvd';

I got error "Field not found -<buyerbrokercompanyname> when Reload. Can someone advice please? I am trying to use Incremental Reload to speed up extraction record. Thanks!

marcus_sommer · ‎2016-08-24

Ok. I think the reason is your renaming of the fields within the sql-satement by using a preceeding load above them. In your example you didn't any transformation within the preceeding load and you could leave them. Another approach would be to transfer all renamings from the sql into the preceeding load - which I would prefer if I had the need of at least one transformation - I mean, are no transformations necessary I wouldn't use a preceeding load and is there any transformation I would do all of them within the preceeding load.

Beside transformations of any kind a renaming of fields could also be done with a renaming-statement maybe:

for i = 1 to nooffields('TradeTBL')

if left(fieldname($(i), 'TradeTBL'), 😎 = 'o??????.' then

let vOldName = fieldname($(i), 'TradeTBL');

let vNewName = capitalize(subfield(fieldname($(i), 'TradeTBL'), '.', 2));

rename fields $(vOldName) into $(vNewName);

end if

- Marcus

Report Inappropriate Content · ‎2016-08-24

Okay, honestly I am only trying to append new records from DB into exsiting records. Could you please provide the suggested script to do this please?

marcus_sommer · ‎2016-08-24

Try:

TradeTBL:
SQL SELECT
o122616.buyerbrokercompanyname AS buyerbrokercompanyname,
o122616.buyeruserid AS Buyeruserid,
o122612.instrument AS instrument,
o122616.orderid AS Orderid,
o122616.price AS Price,
o122610.productname as Product,
o122616.quantity AS quantity,
o122616.sellerbrokercompanyname AS sellerbrokercompanyname,
o122616.selleruserid AS Selleruserid,
o122616.status AS Status,
o122611.stripname AS Strip_Name,
o122616.trade_date AS Trade_Date,
o122616.units AS units,
o122616.iscancelled AS iscancelled,
o122616.isfrombroker AS isfrombroker,
o122616.isfromeditor AS isfromeditor,
o130889.windowid AS windowid,
o130889.windowname AS windowname,
o122616.isclear AS isclear,
o196398.hubname as Hubname,
o122616.sellercompanyname
|| (CASE WHEN o122616.ordertype = 'Offer' THEN '*' ELSE NULL END)
AS Seller,
o122616.buyercompanyname
|| (CASE WHEN o122616.ordertype = 'Bid' THEN '*' ELSE NULL END)
AS Buyer

FROM ewindow.d_product o122610,
ewindow.d_hub o196398,
ewindow.d_strip o122611,
ewindow.d_window_state o122612,
ewindow.t_window o130889,
ewindow.f_transaction o122616

WHERE (o122610.productid = o122616.productid)
AND (o130889.windowid = o122612.windowid)
AND (o122611.stripid = o122616.stripid)
AND (o196398.hubid = o122616.hubid)
AND (o122612.window_state_id = o122616.window_state_id)
AND (o122616.reforderid IS NOT NULL)
AND (o122616.status = 'consummated')
AND (o122616.trade_date >='01-Jan-2010')
AND (o122616.trade_date <='30-Feb-2016')
ORDER BY o122616.trade_date ASC , o122610.productname ASC;

STORE TradeTBL into 'C:\Qlikview\Incremental_Load1.qvd';
drop tables TradeTBL;

TradeTBL:
SQL SELECT
o122616.buyerbrokercompanyname AS buyerbrokercompanyname,
o122616.buyeruserid AS Buyeruserid,
o122612.instrument AS instrument,
o122616.orderid AS Orderid,
o122616.price AS Price,
o122610.productname as Product,
o122616.quantity AS quantity,
o122616.sellerbrokercompanyname AS sellerbrokercompanyname,
o122616.selleruserid AS Selleruserid,
o122616.status AS Status,
o122611.stripname AS Strip_Name,
o122616.trade_date AS Trade_Date,
o122616.units AS units,
o122616.iscancelled AS iscancelled,
o122616.isfrombroker AS isfrombroker,
o122616.isfromeditor AS isfromeditor,
o130889.windowid AS windowid,
o130889.windowname AS windowname,
o122616.isclear AS isclear,
o196398.hubname as Hubname,
o122616.sellercompanyname
|| (CASE WHEN o122616.ordertype = 'Offer' THEN '*' ELSE NULL END)
AS Seller,
o122616.buyercompanyname
|| (CASE WHEN o122616.ordertype = 'Bid' THEN '*' ELSE NULL END)
AS Buyer

FROM ewindow.d_product o122610,
ewindow.d_hub o196398,
ewindow.d_strip o122611,
ewindow.d_window_state o122612,
ewindow.t_window o130889,
ewindow.f_transaction o122616

WHERE (o122610.productid = o122616.productid)
AND (o130889.windowid = o122612.windowid)
AND (o122611.stripid = o122616.stripid)
AND (o196398.hubid = o122616.hubid)
AND (o122612.window_state_id = o122616.window_state_id)
AND (o122616.reforderid IS NOT NULL)
AND (o122616.status = 'consummated')
AND (o122616.trade_date >='01-Mar-2016')
ORDER BY o122616.trade_date ASC , o122610.productname ASC;

Concatenate (TradeTBL)
LOAD * from 'C:\Qlikview\Incremental_Load1.qvd';

STORE TradeTBL into 'C:\Qlikview\Incremental_Load1.qvd';

The renaming of the fields would I do separately like above mentioned.

- Marcus

Report Inappropriate Content · ‎2016-08-24

No more error now ! But I noticed 1 issues...1) Its not loading unique records so am getting double counts

Any suggestions?

marcus_sommer · ‎2016-08-25

You mean duplicates within the first or second load-part? If it runs one time or after (uncommenting the first part and only loading it from the qvd) it runs a second (respectively many) times?

Regarding to your current incremental approach I think you need to store the first part into Incremental_Load1.qvd and after adding the second load-part you need to store in Incremental_Load2.qvd otherwise you would add the second part again and again by each run. If there are any unique ID's available you could change your incremental approach to a check on where (not) exists(ID) - here you will find various examples to incremental loads and optimized load-strategies in combination with exists: Advanced topics for creating a qlik datamodel

- Marcus

jmvilaplanap · ‎2016-08-25

You must find the unique record (maybe order_id) and filter before store.

For example you first load the QVD and then load the data using "WHERE NOT EXIST (order_id)"

Report Inappropriate Content · ‎2016-08-26

Okay, its almost fix now

The only issue left now is the Reload is always reloading all records from DB., which defeats the purpose because then it will take long time. I only want to reload records from May2016-Today. Any suggestions please?

jmvilaplanap · ‎2016-08-26

You can change the trade_date condition in the where clause.