topic Re: Incremental load example in QlikView

Incremental load example

Anonymous — Tue, 30 Aug 2016 05:17:10 GMT

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.

Re: Incremental load example

Anonymous — Tue, 30 Aug 2016 05:20:34 GMT

did you check this?

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

Re: Incremental load example

Anonymous — Tue, 30 Aug 2016 10:33:27 GMT

yes, but this code works somehow strangely..

Please, see attached files

Re: Incremental load example

Anonymous — Tue, 30 Aug 2016 10:41:10 GMT

Have a look

Incremental Load in Qlikview - Sources

Hope it helps!!

Re: Incremental load example

Anonymous — Tue, 30 Aug 2016 10:57:48 GMT

Thanks.

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

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

Re: Incremental load example

Peter_Cammaert — Tue, 30 Aug 2016 11:50:45 GMT

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

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

That should do it.

Peter

Re: Incremental load example

Anonymous — Tue, 30 Aug 2016 11:58:40 GMT

Thanks for advice!

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

Re: Incremental load example

Peter_Cammaert — Tue, 30 Aug 2016 12:08:40 GMT

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

Re: Incremental load example

Anonymous — Tue, 30 Aug 2016 12:40:13 GMT

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.

Re: Incremental load example

Peter_Cammaert — Tue, 30 Aug 2016 12:52:17 GMT

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.