topic Re: Incremental Load in Qlikview in QlikView

Incremental Load in Qlikview

Anonymous — Wed, 07 Feb 2018 09:20:53 GMT

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

stalwar1 loveisfail kaushik.solanki pcammaert marcowedel luismadriz

Re: Incremental Load in Qlikview

tresB — Wed, 07 Feb 2018 09:50:29 GMT

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.

Re: Incremental Load in Qlikview

Anonymous — Wed, 07 Feb 2018 12:06:34 GMT

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

kaushiknsolanki — Thu, 08 Feb 2018 05:42:42 GMT

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

Re: Incremental Load in Qlikview

Anonymous — Thu, 08 Feb 2018 07:02:18 GMT

Thanks Kaushik

I will try it and update you.

Re: Incremental Load in Qlikview

Anonymous — Thu, 08 Feb 2018 09:39:41 GMT

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

Re: Incremental Load in Qlikview

kaushiknsolanki — Thu, 08 Feb 2018 13:19:23 GMT

No we dont need it.

Regards,

Kaushik Solanki

Re: Incremental Load in Qlikview

Anonymous — Thu, 08 Feb 2018 14:29:29 GMT

Thanks for the update kaushik

Re: Incremental Load in Qlikview

Anonymous — Mon, 12 Feb 2018 11:03:10 GMT

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

kaushiknsolanki — Tue, 13 Feb 2018 16:05:52 GMT

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