Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

vireshkolagimat
Contributor III

Incremental load issue

Dear All,

I've implemented the incremental reload to extract data from Oracle EBS. However i am facing below issue.

Ex: I've an order number which was booked on 20-Sep-18 and status of the order was Picked. Later the order got closed and the status got changed to Shipped on say 17-Oct-18.

But still the status of the order is still showing as Picked instead of Shipped.

I've a column Last_modified_date and using the same column to get the max date to perform incremental load.

If do i full reload, it will show the order status as shipped.

Thanks for the suggestion and help.

Regards,

Viresh

Incremental load issue.PNG

7 Replies
sudhakar_budde
Contributor

Re: Incremental load issue

Hi Viresh,

First check all records of that Orderno = 1234 using sql query on your database client (e.g. Toad). This will show you the tracking of the status.

Then check what you are getting for the last_modified_date for that Orderno=1234, this way you know that you are using the correct date field to fetch the latest record.

If the status has changed more than once on a same day, then you may have multiple records? Just check that and adjust your query.

Hope this helps to get some idea on how to debug your issue....

Regards

SB

Partner
Partner

Re: Incremental load issue

Hi Viresh,

As per the screenshot provided, Max(Date) = 20-Sep-2018 and the Status for the same is 'Picking'. so as per the scenario, incremental logic is working fine.

Please provide the incremental logic applied.

Regards

Sumeet

vireshkolagimat
Contributor III

Re: Incremental load issue

Hi Sumeet,

Thanks for the reply. The date for shipped status should be 16 Oct. By mistake i put it as sep.

Below is the script for your reference.

// Set the variables


set _qvd_path = '..\Data\Qvds\Raw_Qvds\';

set _qvd_name = 'Sales_pipeline';

set vKeyField = ORDER_LINE_ID;

set vUpdateField = ORDER_LAST_UPDATE_DATE;


// 1. Check if qvd exists?


let vFileLen = FileSize('$(_qvd_path)$(_qvd_name).qvd');


trace '>> QVD Size: '$(vFileLen);


// 2. If QVD doesn't exist then perform a full reload


if len('$(vFileLen)') = 0 then


trace 'Full reload begins...';


$(_qvd_name):

LOAD * ;

SQL SELECT *

FROM APPS.XXEGC_QV_SALES_PIPELINE_V2;


let vRows = NoOfRows('$(_qvd_name)');


//exit Script


// 3. Max Daate field value into a QVD for the future reload


NewKey:

load

    num#(max(FieldValue('$(vUpdateField)', RecNo()))) as Key

AutoGenerate num#(FieldValueCount('$(vUpdateField)'));


let vNewKey = peek('Key', 0, 'NewKey');


let vKeyQVD = 'OrdersMaxKey_pipeline';


$(vKeyQVD):

load

    '$(vNewKey)' as MaxKey,

    '$(vRows)'   as [# of Rows Inserted],

    date(now())  as [Reload DateTime]

AutoGenerate(1) ; 


STORE  $(vKeyQVD)  into $(_qvd_path)$(vKeyQVD).qvd(qvd);


drop Table NewKey;


// 4. Store table data into QVD

store $(_qvd_name)  into $(_qvd_path)$(_qvd_name).qvd(qvd);


drop table $(_qvd_name);


//5. Reset variables


LET vNewKey = null();

LET vMaxKey = Null();

LET vRows   = null();


exit SCRIPT



// 1. if QVD exists then perform incremental reload


ELSEIF len('$(vFileLen)') > 0 then


TRACE 'Incremental reload begins...';


// 2. Extract Primary Key from the prior reload (stored in a qvd) for the WHERE clause


set vKeyQVD = 'OrdersMaxKey_pipeline';


$(vKeyQVD):

load

   date(MaxKey) as MaxKey

from

$(_qvd_path)$(vKeyQVD).qvd(qvd);


let vMaxKey = Peek('MaxKey');


trace '>>> Last Max Key value stored:  ' & $(vMaxKey);


drop table $(vKeyQVD);


// 3. Perform incremental reload


$(_qvd_name):

LOAD*  ;

SQL SELECT *

FROM APPS.XXEGC_QV_SALES_PIPELINE_V2

where  $(vUpdateField) > trunc(to_date('$(vMaxKey)','DD/MM/YYYY'));

let vRows = NoOfRows('$(_qvd_name)');


trace 'Total Rows:' $(vRows);


NewKey:

load

    num#(max(FieldValue('$(vUpdateField)', RecNo()))) as Key

AutoGenerate num#(FieldValueCount('$(vUpdateField)'));





// 4. Exit gracefully if no new records found


if $(vRows) = 0 then

 

  exit SCRIPT;

 

ELSE


// 5. Concatenate new records with records from the existing QVD


Concatenate($(_qvd_name))

load

   *

from

$(_qvd_path)$(_qvd_name).qvd(qvd)

Where not Exists($(vKeyField));



// 6. Store this table data into QVD


trace 'Storing table data into QVD';


store $(_qvd_name)  into $(_qvd_path)$(_qvd_name).qvd(qvd);


// 7. Store max Primary Key ID for the future incremental reload (QVD)


let vNewKey = peek('Key', 0, 'NewKey');



let vKeyQVD = 'OrdersMaxKey_pipeline';


$(vKeyQVD):

load

    '$(vNewKey)' as MaxKey,

    '$(vRows)'   as [# of Rows Inserted],

    date(now())  as [Reload DateTime]

AutoGenerate(1); 



drop table NewKey;


STORE  $(vKeyQVD)  into $(_qvd_path)$(vKeyQVD).qvd(qvd);


drop table $(_qvd_name);


Thanks.

vireshkolagimat
Contributor III

Re: Incremental load issue

Hi, data looks fine at the back end.. The order status is shipped based on the last modified date.

The incremental load is not over writing the initial status.

Thank you.

lorenzoconforti
Contributor III

Re: Incremental load issue

So basically you want to delete the old record and replace it with the new one? If that's the case the only way I can think of is to delete the updated records first and then to re-run the incremental load. See here: Incremental load to remove deleted records

vireshkolagimat
Contributor III

Re: Incremental load issue

Hi, Thanks for the reply.

In my case the record is not deleted from the source database but only the status is getting changed.

what happens is when the order was booked initially it was in the Picked status and the next day the order got invoiced and status changed from picked to shipped.

Regards,

Viresh

lorenzoconforti
Contributor III

Re: Incremental load issue

Understood, so, in my view you need first to delete the record from your table (i.e. the old record with the Picked status) and then load the incremental data. See the link I posted above