topic Re: Incremental Reload Issue in QlikView

Incremental Reload Issue

Wed, 24 Aug 2016 06:42:59 GMT

//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!

Re: Incremental Reload Issue

marcus_sommer — Wed, 24 Aug 2016 07:01:26 GMT

I think you are missing the file-format by storing and loading the qvd - I mean:

'C:\Qlikview\Incremental_Load1.qvd' (qvd);

- Marcus

Re: Incremental Reload Issue

Wed, 24 Aug 2016 07:05:30 GMT

Okay, could you suggest please what should I do to correct this?

Re: Incremental Reload Issue

marcus_sommer — Wed, 24 Aug 2016 07:26:20 GMT

Add the bolded part - (qvd) - of my above suggestion to your store- and from-statements.

- Marcus

Re: Incremental Reload Issue

Wed, 24 Aug 2016 08:01:53 GMT

Added (qvd) to Store and From statement, same error.

Any other suggestions?

Re: Incremental Reload Issue

jmvilaplanap — Wed, 24 Aug 2016 08:09:31 GMT

Where don't exist this field? In the qvd file or in the database?

Can you open the qvd file in a different document and see all the fields?

Re: Incremental Reload Issue

marcus_sommer — Wed, 24 Aug 2016 08:12:22 GMT

Try it with changing Concatenate into Concatenate(TradeTBL) and also dropping the table after storing them:

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

drop tables TradeTBL,

- Marcus

Re: Incremental Reload Issue

Wed, 24 Aug 2016 08:20:12 GMT

Hi Marcus,

I tried but get error. It would be great if I can call you to discuss? Will be faster..

Re: Incremental Reload Issue

marcus_sommer — Wed, 24 Aug 2016 08:23:55 GMT

How looked the error-message?

ps: one I see here: drop tables TradeTBL, because it's closed with a comma instead of an semi-colon: drop tables TradeTBL;

- Marcus

Re: Incremental Reload Issue

Wed, 24 Aug 2016 08:33:45 GMT

This is the error I got..

Field not found - <buyerbrokercompanyname>

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-2016')

AND (o122616.trade_date <='30-Apr-2016')

ORDER BY o122616.trade_date ASC , o122610.productname ASC

Re: Incremental Reload Issue

marcus_sommer — Wed, 24 Aug 2016 09:06:32 GMT

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

Also possible are renamings per mapping like: Rename fields using MapTab; which after all might be a lot more handier than to rename fields in each single load.

- Marcus

Re: Incremental Reload Issue

Wed, 24 Aug 2016 09:32:28 GMT

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?

Re: Incremental Reload Issue

marcus_sommer — Wed, 24 Aug 2016 10:05:35 GMT

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

Re: Incremental Reload Issue

Thu, 25 Aug 2016 02:07:14 GMT

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

Any suggestions?

Re: Incremental Reload Issue

marcus_sommer — Thu, 25 Aug 2016 05:21:23 GMT

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

Re: Incremental Reload Issue

jmvilaplanap — Thu, 25 Aug 2016 12:24:49 GMT

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

Re: Incremental Reload Issue

Fri, 26 Aug 2016 05:04:00 GMT

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?

Re: Incremental Reload Issue

jmvilaplanap — Fri, 26 Aug 2016 06:38:55 GMT

You can change the trade_date condition in the where clause.