topic Incremental load in QlikView

Incremental load

Anonymous — Sun, 04 Jan 2015 14:57:56 GMT

Good day!

In attached file i have some test model.

When i reload it every day i need to fill such table:

Spoint Date Value

Spoint1 01-01-2014 1 <--------------------When i done reload 01.01.2015

Spoint2 01-01-2014 1

Spoint1 02-01-2014 1 <--------------------When i done reload 02.01.2015

Spoint2 02-01-2014 1

Spoint1 03-01-2014 1 <--------------------When i done reload 03.01.2015

Spoint2 03-01-2014 1

Spoint1 04-01-2014 1 <--------------------When i done reload today

Spoint2 04-01-2014 1

Spoint1 05-01-2014 1 <--------------------When i done reload tomorrow

Spoint2 05-01-2014 1

In other words, i want to add values 1 for each Spoints for dates between firts date of current month and today date (example in xls file)

Many thanks for advices.

Re: Partial load

Colin-Albert — Sun, 04 Jan 2015 16:07:24 GMT

You need to change your script to use Incremental Load.

There is a simple example in this threadIncremental reload and store into QVD file

There are many more articles on the QlikView Community about this, and in the reference manuals.

Re: Partial load

Anonymous — Sun, 04 Jan 2015 16:30:46 GMT

Thanks for your answer.

If you have some time, could you help me to solve my task in example?

Many thanks..

Re: Partial load

its_anandrjs — Sun, 04 Jan 2015 17:28:10 GMT

Hi,

You can use Incremental load concept here for create the rows for the SPoint 1 and 2 and try with this script

1. First load this tables and store the table in QVD

ReloadDetails:

LOAD

ReloadTime() as ReloadTime,

'point 1' as Spoint,

1 as Value

AutoGenerate 1;

LOAD

ReloadTime() as ReloadTime,

'point 2' as Spoint,

1 as Value

AutoGenerate 1;

STORE ReloadDetails into ReloadDetails.qvd;

2. Then load the store qvd table and then concatenate next day

ReloadDetails:

LOAD

Date(ReloadTime(),'DD-MM-YYYY') as ReloadTime,

'point 1' as Spoint,

1 as Value

AutoGenerate 1;

LOAD

Date(ReloadTime(),'DD-MM-YYYY') as ReloadTime,

'point 2' as Spoint,

1 as Value

AutoGenerate 1;

Concatenate(ReloadDetails)

New:

LOAD ReloadTime,

Spoint,

Value

FROM ReloadDetails.qvd (qvd);

STORE ReloadDetails into ReloadDetails.qvd;

Regards

Anand

Re: Incremental load

Anonymous — Sun, 04 Jan 2015 17:37:56 GMT

Thanks for answer.

It's very useful for example, where i have 2 Spoints. In my work model, for example, i will have more than 20 Spoints and i think, it's not very simple for such situation.

Thank you!

Re: Incremental load

its_anandrjs — Sun, 04 Jan 2015 18:39:36 GMT

Hi,

Then try this in the first step

1. First create the QVD with find Max point value in the table suppose it is max upto 10 also

MaxPoint:

LOAD Max(PurgeChar(Spoint,'point')) as MaxPointNum Resident Input;

LET vMax = Peek('MaxPointNum',0,'MaxPoint');

ReloadDetails:

LOAD

Date(ReloadTime(),'DD-MM-YYYY') as ReloadTime,

'point ' &IterNo() as Spoint,

1 as Value

AutoGenerate 1 While IterNo() <= $(vMax);

STORE ReloadDetails into ReloadDetails.qvd;

2. And in this step concatenate the

MaxPoint:

LOAD Max(PurgeChar(Spoint,'point')) as MaxPointNum Resident Input;

LET vMax = Peek('MaxPointNum',0,'MaxPoint');

ReloadDetails:

LOAD

Date(ReloadTime(),'DD-MM-YYYY') as ReloadTime,

'point ' &IterNo() as Spoint,

1 as Value

AutoGenerate 1 While IterNo() <= $(vMax);

Concatenate(ReloadDetails)

LOAD ReloadTime,

Spoint,

Value

FROM

ReloadDetails.qvd

(qvd);

STORE ReloadDetails into ReloadDetails.qvd;

Regards

Anand

Re: Incremental load

Anonymous — Sun, 04 Jan 2015 19:13:26 GMT

Thanks for answer.

I found other solution to rewrite records by last actual date.

SecondLoad:

LOAD Spoint as Data1,

Date,

Value

FROM

(ooxml, embedded labels, table is Sheet1)

WHERE (Date <= $(lastReloadTime));

But after reload it show me error:

Error in expression: ')' expected

Maybe you have some ideas to solve it ?

Re: Incremental load

its_anandrjs — Sun, 04 Jan 2015 19:25:00 GMT

You can use it simply

WHERE Date <= $(lastReloadTime);

And what is the definition of the lastReloadTime time variable and format of the Date and lastReloadTime variable will it match to each other not.

Regards

Anand