Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
sridhar_sigired
Creator
Creator

Incremental load question

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.

1 Solution

Accepted Solutions
balrajahlawat
Champion
Champion

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!!!

View solution in original post

8 Replies
jvishnuram
Partner - Creator III
Partner - Creator III

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.

sridhar_sigired
Creator
Creator
Author

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

sridhar_sigired
Creator
Creator
Author

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.

balrajahlawat
Champion
Champion

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!!!

rgvavihs
Specialist
Specialist

I am not sure try like this

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

jvishnuram
Partner - Creator III
Partner - Creator III

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.

sridhar_sigired
Creator
Creator
Author

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

sridhar_sigired
Creator
Creator
Author

Thank you all for your time.