topic Re: Load / concatenate / update for daily data in QlikView

Load / concatenate / update for daily data

Thu, 26 Jun 2014 09:39:19 GMT

Hi,

I need to load daily data into .QVD files. It's important to load day by day, as new days could contain identical IDs from old files that need to be updated.

What I do currently as pseudo-code:

FOR Every Day

Table:

LOAD Daily_File

CONCATENATE .qvd WHERE ID isn't in File
Store into .qvd

Drop Table

NEXT Day

This works just fine initially to load many days into the .qvd. However, as the .qvd grows bigger and bigger, it's very inefficient/slow to load/save/flush the .qvd 30 times to load a month's data in.

My idea would be to do the above for day 1 only and the following from then on:

FOR Every Day

Table:

LOAD Daily_File

CONCATENATE RESIDENT Table WHERE ID isn't in File

NEXT Day

Store into .qvd

The issue with that is that it creates "Table-1", "Table-2" etc. as the names already exist. Is there a way to open an existing table? Or a better way to solve my problem?

Thanks!

Oli

Re: Load / concatenate / update for daily data

fosuzuki — Thu, 26 Jun 2014 15:33:28 GMT

I suppose that if your code is something like this:

FOR Every Day

Table:

LOAD Daily_File

WHERE Not Exists(ID)

NEXT Day

Store into .qvd

It should work, because QV would automatically concatenate each load into the same table, and would only include the IDs that doesnt yet exist. But the auto concatenate will only happen if the daily files structure (columns) is identical.

Re: Load / concatenate / update for daily data

shawn-qv — Thu, 26 Jun 2014 23:34:16 GMT

Perhaps look at doing an incremental load.

You didn't specify what data source you're working with, so for simplicity sake, I'm going to assume an SQL table.

This process will require you to have a modification date/time information in order to tell when a record was last updated.

Table:

SQL SELECT ID, Daily_File

FROM DB_TABLE

WHERE ModificationTime >= #$(vLastExecTime)#;

concatenate load

ID,

Daily_File

from Table.qvd

where not exists(ID);

store Table into Table.qvd;

** vLastExecTime is the last updated time stamp (i.e. yesterday's date), and can be returned by doing something like max(ModificationTime) from the existing qvd file (i.e. Table.qvd).

This process doesn't not handle deletion when records are deleted from the source data. An additional inner join will need to be performed if this is required.

Re: Load / concatenate / update for daily data

nandhaadjame200 — Wed, 31 Oct 2018 11:44:57 GMT

Sorry Shawn & Fernando to interrupt in this thread.

I've same issue, like not concatenating but new records are adding. But i'm handling with csv to QVD only not SQL server.

Please help to fix this issue.

Thanks

Nandhakumar