Incremental Load in Qlikview

[Anonymous]

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

[tresesco]

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.

[Anonymous]

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

[kaushiknsolanki · Accepted solution]

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

[Anonymous]

Thanks Kaushik

I will try it and update you.

[Anonymous]

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

[kaushiknsolanki]

No we dont need it.

Regards,

Kaushik Solanki

[Anonymous]

Thanks for the update kaushik

[Anonymous]

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

[kaushiknsolanki]

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