Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
marcus_sommer

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

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

- Marcus

Not applicable
Author

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

marcus_sommer

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

- Marcus

Not applicable
Author

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

Any other suggestions?

jmvilaplanap
Specialist
Specialist

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?

marcus_sommer

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
Author

Hi Marcus,


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

marcus_sommer

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
Author


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