Incremental load example

[Anonymous]

Colleagues, good day!

I have some simple example to implement incremental load for main model.

I have only two fields: Date and Sales.

Also i've created qvd with all start values from source file.

Now i need update this qvd file only for those dates, which month number i want to set in script.

For example:

set vLoadPeriod=1

if vLoadPeriod=1, my qvd must update values for current month,

if vLoadPeriod=2, my qvd must update values for current month and previous,

if vLoadPeriod=3, my qvd must update values for current month, previous and pre previous,

Could you please help me with it?

Thanks.

[Anonymous]

did you check this?

https://www.resultdata.com/three-types-of-qlikview-incremental-loads/

[Anonymous]

yes, but this code works somehow strangely..

Please, see attached files

[Anonymous]

Hi

Have a look

Incremental Load in Qlikview - Sources

Hope it helps!!

[Anonymous]

Thanks.

It nice works if i use some values from inline load.

But incremental load from xlsx works not correctly in my example.

[Peter_Cammaert]

A few steps, just to give you an idea on how to create a solution:

1. Make sure that vLoadPeriod is numerical and > 0
2. From Excel, load all rows WHERE Date >= AddMonths(MonthStart(Today()),1-vLoadPeriod)
3. From QVD, add all rows that WHERE Not Exists(Date)
4. Store the resident table into the existing QVD

That should do it.

Peter

[Anonymous]

Thanks for advice!

All of these steps are implemented, but in result i have not correct sales for "old" data

[Peter_Cammaert]

That's to be expected. You were describing a solution with a Date field (e.g. Year+Month+Day) and a control variable that determines how many months the update should go back.

But the actual data only has a Year field. Which means that the solution for Year-Month-Day values will stop adding rows from your QVD as soon as it finds out that it already has a row for that particular year. The result will be that only one row per year will be loaded from the history file. Wrong granularity.

Fix your data and you will see that it works like a charm.

Peter

[Anonymous]

At first In attached files i try to load only "not updated" data by operator Which not exists (Year) (for understanding how incremental load works), only after that i will works with rollback condition.

And i don't understand, why script doesn't work with Year.

[Peter_Cammaert · Accepted solution]

The script actually does work. But since it checks whether the year in the current row from your QVD already exists (in order to avoid overwriting update records), and since Exists() operates on fields, it will find the same year from the previous row and stop loading additional rows from that same year. Every row loaded through a LOAD statement will immediately update the field values.

Since your history file contains a lot of rows from both 2015 and 2016, the Exists() will force the load of a single row for every year (actually the first one in the file). All others will be thrown away.

If you were using a real date field, the incremental load would check on date values. And since we assume that both the update and the history file keep only on row per date value, the incremental load would work just fine. But not with years.