Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Incremental Reload Issue

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

17 Replies

Re: Incremental Reload Issue

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

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

- Marcus

Highlighted
Not applicable

Re: Incremental Reload Issue

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

Re: Incremental Reload Issue

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

- Marcus

Not applicable

Re: Incremental Reload Issue

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

Any other suggestions?

jmvilaplanap
Valued Contributor

Re: Incremental Reload Issue

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

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

Not applicable

Re: Incremental Reload Issue

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

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

Not applicable

Re: Incremental Reload Issue


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

Community Browser