topic Re: incremental load different customers on different dates in QlikView

incremental load different customers on different dates

Mon, 17 Mar 2014 08:16:12 GMT

Hello,

I have one giant view which contains data for 20 customers. All these customers has a CustomerKey and a datefield and more.

Due to millions of rows i wanna do an incremental load. But the thing is, if one customer fails the date might be earlier than the other customers dates. therefore i need to incremental load my view for each customers latest date. How can i do so?

Im guessing i should make some kinda loop, but how i should write i dont know? Can somebody help?

Latest Load Date Table

LatestRunLogKey	LatestDateLoadFrom
1	2014-03-14 12:00:00.000

Example of the table:

CustomerKey	Datefield	Sales	ProductName
1	2014-03-14 12:00:00.000	10	Cheese
1	2014-03-14 13:00:00.000	10	Cheese
1	2014-03-14 14:00:00.000	10	Cheese
2	2014-03-14 12:30:00.000	10	Cheese
2	2014-03-14 14:37:00.000	10	Cheese
2	2014-03-14 12:53:00.000	10	Cheese
3	2014-03-10 12:00:00.000	10	Cheese
3	2014-03-10 12:00:00.000	10	Cheese

So as you can see CustomerKey 3 data will not be loaded cause the latest last time load date was 2014-03-14 12:00:00.000 - and because customerkey 3 failede last time they did not have any data in the view. Now they do but its older than the latestdateloadfrom date.

Re: incremental load different customers on different dates

Wed, 19 Mar 2014 06:54:04 GMT

Is there really no one with any idea on how to solve this?

Re: incremental load different customers on different dates

daveamz — Wed, 19 Mar 2014 07:12:57 GMT

Hi Thomas,

Maybe you should load Datefiled > LatestDateLoadForm - couple of days.

Or, you can extract data per months or weeks and then concatenate them, whatever it works better for your scenario.

Best regards,

David

Re: incremental load different customers on different dates

Wed, 19 Mar 2014 07:15:52 GMT

- couple of days isnt a very good solution, so that is not a option:/

Re: incremental load different customers on different dates

maxgro — Wed, 19 Mar 2014 07:27:05 GMT

just an idea

starting from incremental load type 2, you find it in the help

1) load your qvd (the one you store yesterday)

2) load your qvd group by customer, you need the max date for every customer

3) loop on resident table of step 2

for every customer

read from the db using customer and max date of customer (one select with 2 variable)

store in a qvd (one qvd for all customer; this qvd has the incremental data of all customers)

4) append the qvd you make at step 3 to qvd read at step 1 and store it for next reload

Re: incremental load different customers on different dates

daveamz — Wed, 19 Mar 2014 07:28:33 GMT

And to extract data into months/weeks series?

I can't see how to do an incremental load without and row unique id or time stamp field.

More info about Incremental Load http://www.quickintelligence.co.uk/qlikview-incremental-load/

Re: incremental load different customers on different dates

Thu, 20 Mar 2014 13:01:54 GMT

There is a datefield. But nevermind ill try something else out. Thansk for the response.