Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
javier_florian
Contributor III

Incremental Load

Hi Everyone,

Can you help me to check if script attached is fine? I have a doubt related validation (NOT Exists(PK)) in order to have an unique PK and don't loss any row.

Thanks in advanced.

-JFlorian

Tags (1)
1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Incremental Load

Hi Javier,

The code you have as the first part of your load when a QVD exists and the code when no QVD exists looks very similar.  If you could just have that code once it would remove the chance of things getting changed in one part and not another.

Where you are checking for the QVD and where you load from the QVD it is different code, ie.

Upload_Incremental_From_$(vQuarter).qvd

and

$(vName)$(vQuarter)$(vExt)

I would make this the same, either use the variables or the fixed text.

Where you have commented out the WHERE NOT EXISTS there is the risk of duplicate rows coming in if you pull the same row from the source table twice.  I see you are trying to ensure this isn't the case by deriving the Min Date.  This is all well and good as long as the FILEPRC_PRC_STR_DT field can't be messed with and give a duff value at some point.

The flip side of WHERE NOT EXISTS is that if the field is not totally unique you will loose rows, so even if there are two rows in the source table and you attempt to load them at the same time WHERE NOT EXISTS(ID) would cause only the first found row to load.

As long as you are happy with the assertions you have made about the source data then it looks like a solid incremental load.  Obviously the best thing to do is to reconcile the row counts in your QVD's back to the RowCounts in your source table - just to be sure.

I've put a number of thoughts on incremental loads in a blog post - I will post a link to the blog up here in a separate post - it may take a while to be moderated though.

Hope that helps.


Steve

View solution in original post

2 Replies
MVP & Luminary
MVP & Luminary

Re: Incremental Load

Hi Javier,

The code you have as the first part of your load when a QVD exists and the code when no QVD exists looks very similar.  If you could just have that code once it would remove the chance of things getting changed in one part and not another.

Where you are checking for the QVD and where you load from the QVD it is different code, ie.

Upload_Incremental_From_$(vQuarter).qvd

and

$(vName)$(vQuarter)$(vExt)

I would make this the same, either use the variables or the fixed text.

Where you have commented out the WHERE NOT EXISTS there is the risk of duplicate rows coming in if you pull the same row from the source table twice.  I see you are trying to ensure this isn't the case by deriving the Min Date.  This is all well and good as long as the FILEPRC_PRC_STR_DT field can't be messed with and give a duff value at some point.

The flip side of WHERE NOT EXISTS is that if the field is not totally unique you will loose rows, so even if there are two rows in the source table and you attempt to load them at the same time WHERE NOT EXISTS(ID) would cause only the first found row to load.

As long as you are happy with the assertions you have made about the source data then it looks like a solid incremental load.  Obviously the best thing to do is to reconcile the row counts in your QVD's back to the RowCounts in your source table - just to be sure.

I've put a number of thoughts on incremental loads in a blog post - I will post a link to the blog up here in a separate post - it may take a while to be moderated though.

Hope that helps.


Steve

View solution in original post

MVP & Luminary
MVP & Luminary

Re: Incremental Load

The blog post on Incremental Loads which I referred to previously can be found here:

http://www.quickintelligence.co.uk/qlikview-incremental-load/

There is example code for the three main ways of creating an incremental load strategy and plenty of discussion in the comments which you may find interesting.

- Steve