Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Re: Incremental Reload Issue

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'), 8) = 'o??????.' then

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

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

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

     end if

next

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

Not applicable

Re: Incremental Reload Issue

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

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

Not applicable

Re: Incremental Reload Issue

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

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

Highlighted
jmvilaplanap
Valued Contributor

Re: Incremental Reload Issue

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

Not applicable

Re: Incremental Reload Issue

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
Valued Contributor

Re: Incremental Reload Issue

You can change the trade_date condition in the where clause.

Community Browser