Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
ioannagr
Creator III
Creator III

incremental load case

Hello I have a simple qvd like

Qvd:

Load id, date

from blabla.qvd;

on which i want to do incremental load. First, i though by id, but they aren't sequential, meaning the biggest id isn't the most recent one, so creating a variable and do "where id>'$(MaxID';" doesn't serve me.

Secondly I tried with LastDate, but the thing is the LastDate is a test one coming from 2036, so no new data is found always.

What should i do? I thought about Filetime() on the initial qvd, what are your ideas?

 

Thank you in advance.

6 Replies
jwjackso
Specialist II
Specialist II

Look at the Where Not Exists function

chriscammers
Partner
Partner

Your example has some pretty tough limitations, without a specific way to identify changes in the source data it is very difficult to implement an incremental load process. The best you can hope for is comparing a previously loaded qvd file to one that is more recently loaded and then creating flags for new and deleted records.

 

I would load the previous qvd and then do a full outer join of the new qvd

BlaBla:
Load id, date, 1 as inOldFile
from old_blabla.qvd;

//full outer join based on only the id field
Join(BlaBla)
Load id, date as NewDate, 1 as inNewFile
from new_blabla.qvd;

//new table to calculate some important measures related to file comparison
BlaBlaBla:
Load
   id,
   If(isnull(NewDate),date,NewDate) as date
   If(Isnull(inNewFile),1,0) as isDeleted,
   If(Isnull(inOldFile),1,0) as isNewRecord,
   If(not isnull(inOldFile) and Not isnull(inNewFile) and date <> NewDate,1,0) as isChanged
resident BlaBla;
rwunderlich

It's unusual to do an incremental load from a QVD. Incremental loads are typically done from a database. Can you describe your use case?

-Rob

chriscammers
Partner
Partner

@rwunderlich fair, I was being a little lazy in my example.

ioannagr
Creator III
Creator III
Author

Hi, my english is at times not so well; Forgive me.

I want to do incremental load for a qvd (if that's better).

MaxKeyLoad:
 Load Max(id) as MaxID
from [$(vPathQVD)MyQvd.qvd] (qvd);

Let MaxID = Peek('MaxID', 0 , MaxKeyLoad);
Trace $(MaxID);

MyTable:
 LOAD id ,
creationdate;


SQL SELECT id,
creationdate
FROM public.mytable
where id>'$(MaxID)';   

concatenate 

Load * from [$(vPathQVD)MyQvd.qvd] (qvd)

where not exists(id);

That was the first try which didn't give me correct results as the biggest id isn't the most recent.

 

Then tried with date

Last_Date:

Load timestamp(Max(date(creationdate,'DD/MM/YYYY h:mm:ss TT'))) as MaxDate
from [$(vPathQVD)MyQvd.qvd] (qvd);

Let Last_Date=timestamp(date(Peek('MaxDate',0,Last_Date)),'DD/MM/YYYY h:mm:ss TT');
Trace $(MaxDate);

 

MyTable:
 LOAD id ,
creationdate;


SQL SELECT id,
creationdate
FROM public.mytable
where creationdate>'$(MaxDate)';   

 

concatenate 

Load * from [$(vPathQVD)MyQvd.qvd] (qvd)

where not exists(id);

this also can't work because today is 14.6 and the max date is in 2036 so no new data is loaded.

 

 

How to handle incremental load now?

@chriscammers  @rwunderlich  @jwjackso   (also thanks for your time)

 

rwunderlich

Perhaps create an update field in the database table, such as  SQL Server rowversion or  Oracle SCN. What database product are you using?

-Rob