topic Re: Create separate .qvd files to eventually concatenate in App Development

Create separate .qvd files to eventually concatenate

kdaniels-obrien — Sat, 16 Nov 2024 03:26:41 GMT

Hi,

I am looking to use one qvd file to store 12 hours of data. Then at the end of 12 hours, I want to concatenate that data with another qvd file that serves as a historical data file. I'm hoping this will speed up my load time. Can someone help me get started with this process? How do I specify when the 12 hour data should be moved to the historical data? What are some key commands/functions others have used when doing this?

Thank you!

Re: Create separate .qvd files to eventually concatenate

Vegar — Mon, 27 Jan 2020 22:14:35 GMT

I think you could be able to speed up your load. Important when doing this is to keep track on which transactions are sent to the historical qvd and which are not. Timestamps and transaction IDs are both good indicators on which data is loaded in the QVD.

I suggest you start reading up on a topic called incremental load. You will find a lot of post on incremental loads in this community, You will find a lot on the topic outside of the Qlik sphere as well, but I reccomend you stick to the Qlik related postings to begin with.

Good luck.

Re: Create separate .qvd files to eventually concatenate

kdaniels-obrien — Fri, 31 Jan 2020 18:08:53 GMT

Would something like this in my load script achieve what I described in the post?

Earlier in my load script, I get the MaxTime from the last load... I want to collect data all day long and then when it is a new day, move those records from the last 24 hr qvd to the other qvd file that has all time data.

if date(floor(MaxTime)) <> date(floor(Today())) then
[Last 24 Hours]:
LOAD *
from 'lib://..../24Hours.qvd' (qvd);
concatenate([Last 24 Hours])
LOAD *
from 'lib://.../AllTime.qvd' (qvd);
DROP TABLE [Last 24 Hours];

Re: Create separate .qvd files to eventually concatenate

Vegar — Fri, 31 Jan 2020 21:32:01 GMT

I think this might be a more robust solution if you are running this script multiple times a day.

MaxTimeTable:
LOAD max(Time) as MaxTime
FROM LIB://.../AllTime.qvd (qvd);

LET vMaxTime = peek('MaxTime', -1, 'MaxTimeTable'); //Latest transaction time

Data:
LOAD *
From LIB://.../24Hours.qvd (qvd)
Where Time > $(vMaxTime);

Concatenate (Data)
LOAD *
From LIB://.../AllTime.qvd (qvd)
;

Store Data into LIB://.../AllTime.qvd (qvd);

You could take a look at this YouTube film: https://www.youtube.com/watch?v=GLO2UteiHQ8