topic Re: Incremental Load in QlikView

Incremental Load

navaneeth79 — Tue, 13 Feb 2018 11:30:28 GMT

I have a QVW where the excel data is loaded and stored in qvd from many sheets of excel sheet using odbc connection. Now I want the qvd to be incremented with the latest data retaining the old data in the qvd when the excel is replaced.

Re: Incremental Load

swuehl — Tue, 13 Feb 2018 12:08:03 GMT

Have you alread had a look at

Using QVD files for incremental load ‒ QlikView

Re: Incremental Load

navaneeth79 — Tue, 13 Feb 2018 16:22:41 GMT

I have followed the same procedure but unable to append the data, only new data is been seen.

Re: Incremental Load

Thiago_Justen_ — Tue, 13 Feb 2018 16:47:01 GMT

What about this:

www.analyticsvidhya.com/blog/2014/09/qlikview-incremental-load/

Re: Incremental Load

navaneeth79 — Tue, 13 Feb 2018 16:54:40 GMT

my query is every week I replace the source file so the last week data gets replaced by new week data. but I want to retain the old data of the previous file and the new data in the current file should get incremented to it

Re: Incremental Load

Thiago_Justen_ — Tue, 13 Feb 2018 17:02:11 GMT

I see...

I have an app doing what you want. Basically, if you follow the steps into the link below you will reach your goal:

Overview of Qlik Incremental Loading

Re: Incremental Load

navaneeth79 — Wed, 14 Feb 2018 15:51:59 GMT

its not working

I have a excel with 5 sheets and all sheets have same fields, and I get each excel every week. presently we replace old excel with new excel. and the 5 sheets data is store into one table in qlikview using ODBC connection. and that table is stored in QVD. Now I want the exact procedure but only change is need to retain the old data in QVD as well as increment the QVD with new data.

Re: Incremental Load

swuehl — Wed, 14 Feb 2018 16:50:46 GMT

Please outline your current approach of loading the data.

There are a lot of working examples of an incremental LOAD, so it should be possible to adapt to your needs.

Re: Incremental Load

navaneeth79 — Thu, 15 Feb 2018 08:02:00 GMT

ODBC CONNECT TO [Excel Files;DBQ=$(FilePath)\ResponseTimes.xlsx];

XlsxTables:
SQLTables;

DISCONNECT;

LET vRows = NoOfRows('XlsxTables');
FOR i = 0 TO $(vRows)-1
LET vSheetName = subfield(peek('TABLE_NAME', i,'XlsxTables'),'$',1);

ResponseTime:
LOAD *,
'APResponse' as FactType,
Time as ResponseTimestamp,
hour(frac(Time)) as TestHour,
frac(Time) as TestTime,
[Resp Time] as ResponseTime,
Date(Time) as _CalendarDateKey,
'$(vSheetName)' as PerfTestName
FROM [$(FilePath)\ResponseTimes.xlsx]
(ooxml, embedded labels, table is '$(vSheetName)');

NEXT i

DROP TABLE XlsxTables;

Concatenate (ResponseTime)
LOAD * from [$(QVDPath)\ResponseTime.qvd](qvd);

Store ResponseTime into
[$(QVDPath)ResponseTime.qvd]
(qvd);

Drop Table ResponseTime;

This is my code where ReponseTimes.xlsx is the excel work book with 5 sheets containing same fields and every week the Old Excel is replaced by new Excel and Data is stored in ResponseTime.qvd . But now my requirement is whenever old excel is replaced by new excel the qvd should hold the old data along with new data

Re: Incremental Load

shiveshsingh — Thu, 15 Feb 2018 11:00:23 GMT

In your below code, what is there in ResponseTime.qvd? Old files?

Concatenate (ResponseTime)
LOAD * from [$(QVDPath)\ResponseTime.qvd](qvd);

Re: Incremental Load

navaneeth79 — Thu, 15 Feb 2018 11:35:39 GMT

old data fetched from old excel workbook

Re: Incremental Load

shiveshsingh — Thu, 15 Feb 2018 12:06:17 GMT

But here you are already concatenating the new file data with old file, then what's the issue?

Re: Incremental Load

navaneeth79 — Thu, 15 Feb 2018 12:08:09 GMT

the old data is getting replaced by new data instead of appending

Re: Incremental Load

swuehl — Thu, 15 Feb 2018 12:09:08 GMT

Maybe you are just missing a backslash here in your path?

Store ResponseTime into
[$(QVDPath)ResponseTime.qvd]
(qvd);

Re: Incremental Load

navaneeth79 — Thu, 15 Feb 2018 12:10:38 GMT

I have placed everything right, while removing comments I by mistakenly removed the backslash

Re: Incremental Load

stabben23 — Thu, 15 Feb 2018 12:10:58 GMT

Hi,

when you concatenate the Old data, you should have a Where not Exists(Keyfield).

Keyfield is here probabll ResponseTimestamp

Concatenate (ResponseTime)
LOAD * from [$(QVDPath)\ResponseTime.qvd](qvd)

Where not Exists(Keyfield).

;

Re: Incremental Load

swuehl — Thu, 15 Feb 2018 12:22:26 GMT

If you want to keep all historic data, there is no need for a WHERE NOT EXISTS() clause.

Re: Incremental Load

shiveshsingh — Thu, 15 Feb 2018 12:28:36 GMT

Your code seems fine, dunno why you are getting such issue, need to debug!

Let me check

Re: Incremental Load

navaneeth79 — Fri, 16 Feb 2018 12:10:15 GMT

I removed drop table and checked the data in a straight table and can see that old data is also present along with new data but when I open the QVD I cant find the old data

Re: Incremental Load

swuehl — Fri, 16 Feb 2018 15:07:55 GMT

Double check that the QVD is actually updated by your script (Last Modification Timestamp).

I still have the doubt that you are storing the QVD to another location than you are reading from.