Incremental Reload Issue

[#1]

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

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

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

- Marcus

[#3]

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

[#5]

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

Any other suggestions?

[jmvilaplanap]

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

[#8]

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

[#10]

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