topic Re: Incremental load question in QlikView

Incremental load question

sridhar_sigired — Tue, 29 Dec 2015 09:57:46 GMT

Hi,

I have an issue with pulling the deleted records from tables. Here is my scenario.

I have two tables.

SALES Table - Which contains All records (For bulk load i do reload all records from here) - Inserted and updated dates are available for new and updated records.

SALES_AUDIT - Which contains deleted records with deleted_date. These records physically deleted from SALES table in database itself.

While loading full load,

I am creating SALES.QVD (From Sales_Table) and SALES_DELETED.QVD (From SALES_AUDIT table)

While doing incremental load,

i am using >max(deleted_date) and <Sysdate to get the deleted records in particular period from existing deleted QVD. And removing them from mail QVD.

While doing next incremental load, the existing deleted records available in Deleted QVD are replaced with new set of deleted records (as i storing on the same QVD)

Now the issue is, when there are zero records available in deleted QVD, I have no deleted_date information to show max(deleted_date).

If zero deleted records are there, i want to keep old records in deleted QVD. Is it possible??

If not, how can i have max(deleted_date) from previous reload time???

Any suggestion would be helpful to my whole work.. Thanks.

Note: Already same kind of question asked few days back, but did not resolve. Opening new thread to reach more people. Thanks.

Regards,

Sridhar.

Re: Incremental load question

jvishnuram — Tue, 29 Dec 2015 10:24:43 GMT

Hi Sridhar,

What you are asking is possible, you have to concatenate the newly deleted record with yesterday's deleted record.

For example DAY 1(Today) have 50 deleted record with the date today and this is stored in the place called ../Sales/Sales_Audit.qvd

Then on day 2

DAY 2(Tomorrow) have 25 deleted record with tomorrow's date

then

the query will be like this.,

ORG:

Load Deleted records

from ../Sales/Sales_Audit.qvd;

Concatenate

Load Deleted records

From sales where >max(deleted_date) and <Sysdate;

It will keeps your old record as well as incremental records

Hope this will help

Regards,

Vishnuram Jayaraman.

Re: Incremental load question

sridhar_sigired — Tue, 29 Dec 2015 10:34:46 GMT

Hi Vishnu,

Thank you for your reply.

Actually my concern is, i have millions of deleted records in my table. So i don't want to keep all records by doing concatenation.

I want to replace existing records with new set... i can do that by storing on same QVD.

However when ZERO records are available, i will be having problem. So i need to keep old records when i have zero records to store.

For example:

While Full load of deleted records on Jan 1st 2016 - I get 10 million records

There onward i need to do incremental load daily.

2nd Jan - I got 1 million deleted records (I will store on 10 million records QVD now it will be 1 million records)

3rd Jan - I got 10K records (I will store on 1 million records QVD now it will be 10K records)

There is no problem until i get more than 1 records in my incremental load, because i can pull MAX date from that.

4th Jan - I got 0 records (it will be storing on 10K deleted records QVD now it will be 0 records)

I will not get MAX date in next incremental load.... so i wanna keep 10K records as it is to get MAX date.

Or i need to get MAX date from that 10K records in any other way...

Hope this is clear.

Thanks.

Sridhar

Re: Incremental load question

sridhar_sigired — Tue, 29 Dec 2015 10:42:30 GMT

Can we use if condition to store the QVD?

Like if records are zero then i will not store into QVD and old QVD exists.

Any suggestions?

Like this....

Sales_Deleted:

ID,

Deleted_Time

from SALE_Deleted Table

where Deleted_Time> 'vMax(Deleted_Time)' and Deleted_Time<=sysdate;

If Count(ID)<0,

Store Sales_Deleted into Sales_Deleted.QVD

Drop Table Sales_Deleted.QVD

Thanks.

Re: Incremental load question

Anonymous — Tue, 29 Dec 2015 10:49:12 GMT

may be like this?

Sales_Deleted:

ID,

Deleted_Time

from SALE_Deleted Table

where Deleted_Time> 'vMax(Deleted_Time)' and Deleted_Time<=sysdate;

let test=NoOfRows('Sales_Deleted');

If $(test) > 0 then

Store Sales_Deleted into Sales_Deleted.QVD

end if

Drop Table Sales_Deleted.QVD;

Hope this will help!!!

Re: Incremental load question

Anonymous — Tue, 29 Dec 2015 11:04:16 GMT

I am not sure try like this

if((No.of.Rows('TableName'))<0

Re: Incremental load question

jvishnuram — Tue, 29 Dec 2015 11:11:37 GMT

Hi Sridhar,

Then before storing the qvd check the condition record should not be zero that's it, and it is possible also.

Regards,

Vishnuram Jayaraman.

Re: Incremental load question

sridhar_sigired — Tue, 29 Dec 2015 11:31:22 GMT

Can you please give me a condition, how it looks like. That will be helpful.

Re: Incremental load question

sridhar_sigired — Tue, 29 Dec 2015 13:00:56 GMT

Thank you all for your time.