Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
narenjln
Contributor

Incremental Load in Qlikview

Hi I want to Implement the Incremental Load in Qlikview

My Coding Sample will be like Below,

Let vFile1 = $(vFilePath1)\Sample1.qvd(qvd);

if (isnull(FileSize('$(vFile1)'))) then

TRACE 'file doesn't exists';

Sample1:

Load

ID,

Name,

Age,

Date_Updated

from

[$(vFilePath)$(vSM).csv]

(txt, utf8, embedded labels, delimiter is '|', no quotes, no eof);

Store Sample1 into$(vFilePath1)\Sample1.qvd(qvd);

Drop Sample1

Else

Sample1:

Load

ID,

Name,

Age,

Date_Updated

from

[$(vFilePath)$(vSM).csv]

(txt, utf8, embedded labels, delimiter is '|', no quotes, no eof)

Where last_updated >=Date_updated;

Concatenate

Sample1:

Load

ID,

Name,

Age,

Date_Updated

from

$(vFilePath1)\Sample1.qvd(qvd)

Where not exists(ID);

Store Sample1 into$(vFilePath1)\Sample1.qvd(qvd);

Drop Sample1

My Query is how can i capture the Last_updated date , Becoz here i am using the 2 Load Statement how can i achieve this



Normally i will capture the updated_Date last reload  date and store it into the varaible


but now i am using 2 load function how can i achieve this


stalwar1loveisfailkaushik.solankipcammaertmarcowedelluismadriz

1 Solution

Accepted Solutions

Re: Incremental Load in Qlikview

Hi,

You have missed the coder to find the Load Date.

Logic goes like this.

If QVD not exists then create a new

else

Get the max date from QVD;

Store it in variable as last reload;

Get the new data from Database where date > Last reload (Variable)

Concate

The existing QVD;

Store the table in QVD

Drop the table;

So your code should look like below.

Let vFile1 = $(vFilePath1)\Sample1.qvd(qvd);

if (isnull(FileSize('$(vFile1)'))) then

TRACE 'file doesn't exists';

Sample1:

Load

ID,

Name,

Age,

Date_Updated

from

[$(vFilePath)$(vSM).csv]

(txt, utf8, embedded labels, delimiter is '|', no quotes, no eof);

Store Sample1 into $(vFilePath1)\Sample1.qvd(qvd);

Drop Sample1;

Else

Max:

Load Max(Date_Updated) as MaxDate

From $(vFilePath1)\Sample1.qvd(qvd);


Let vLastReload = Peek('MaxDate',0,'Max');

Sample1:

Load

ID,

Name,

Age,

Date_Updated

from

[$(vFilePath)$(vSM).csv]

(txt, utf8, embedded labels, delimiter is '|', no quotes, no eof)

Where Date_updated > last_updated;

Concatenate (Sample1)

Load

ID,

Name,

Age,

Date_Updated

from

$(vFilePath1)\Sample1.qvd(qvd);

Store Sample1 into$(vFilePath1)\Sample1.qvd(qvd);

Drop Sample1,Max;

End IF.

Regards,

Kaushik Solanki

9 Replies
MVP
MVP

Re: Incremental Load in Qlikview

I am not sure I understand what you meant. To get the last update timestamp from two tables, you have to concatenate the tables (you are already doing so), then get the max timestamp out of the resultant table.

narenjln
Contributor

Re: Incremental Load in Qlikview

I am confused with my query that y I posted here

First source table is to load the data if qvd not exists in the path

Second source table reading for if qvd exists in the path

Then I am compaing It with the qvd data for incremental load

Know my clarification is from which source table I should pickup the last upated date (table1 or table 2)

Or is there any other option for that

Kindly help me out

Re: Incremental Load in Qlikview

Hi,

You have missed the coder to find the Load Date.

Logic goes like this.

If QVD not exists then create a new

else

Get the max date from QVD;

Store it in variable as last reload;

Get the new data from Database where date > Last reload (Variable)

Concate

The existing QVD;

Store the table in QVD

Drop the table;

So your code should look like below.

Let vFile1 = $(vFilePath1)\Sample1.qvd(qvd);

if (isnull(FileSize('$(vFile1)'))) then

TRACE 'file doesn't exists';

Sample1:

Load

ID,

Name,

Age,

Date_Updated

from

[$(vFilePath)$(vSM).csv]

(txt, utf8, embedded labels, delimiter is '|', no quotes, no eof);

Store Sample1 into $(vFilePath1)\Sample1.qvd(qvd);

Drop Sample1;

Else

Max:

Load Max(Date_Updated) as MaxDate

From $(vFilePath1)\Sample1.qvd(qvd);


Let vLastReload = Peek('MaxDate',0,'Max');

Sample1:

Load

ID,

Name,

Age,

Date_Updated

from

[$(vFilePath)$(vSM).csv]

(txt, utf8, embedded labels, delimiter is '|', no quotes, no eof)

Where Date_updated > last_updated;

Concatenate (Sample1)

Load

ID,

Name,

Age,

Date_Updated

from

$(vFilePath1)\Sample1.qvd(qvd);

Store Sample1 into$(vFilePath1)\Sample1.qvd(qvd);

Drop Sample1,Max;

End IF.

Regards,

Kaushik Solanki

narenjln
Contributor

Re: Incremental Load in Qlikview

Thanks Kaushik

I will try it and update you.

narenjln
Contributor

Re: Incremental Load in Qlikview

Kaushik whether we need to have that max table after storing it to variable

Re: Incremental Load in Qlikview

No we dont need it.

Regards,

Kaushik Solanki

narenjln
Contributor

Re: Incremental Load in Qlikview

Thanks for the update kaushik

narenjln
Contributor

Re: Incremental Load in Qlikview

kaushik.solanki

I have little doubt kindly clarify me ,

I have stored the last modified date in the variable

For ex:11/02/2018 is stored in variable

When I am refreshing today and inserting the new record with the date of 12/2/2018

And I  need the variable to store the recent date

But variable is holding the 11/02/2018

Kindly help me out

Re: Incremental Load in Qlikview

Hi,

It will not because the max date is picked up from QVD (the last record date which you have fetched earlier)

Once you run the script and store the new record i.e 12/2/2018 in your QVD after that it will show you that as max date in next reload.

Regards,

Kaushik Solanki