topic Store tables into one QVD in App Development

Store tables into one QVD

andreasthisell — Tue, 13 May 2025 18:16:09 GMT

I have several tables in my Qlik today.
It contains data from article register, forecast, stock etc.

When I run my qvw i have today it shows todays information.

I want to load all data from all tables, store them all in one QVD file, save as for example 2025-05-13-20:00 and drop all tables.

Afterwards I would like to select from all stored QVDs in my qvw to look at the values that were according to the saved QVD files date/time, this to compare values from each day.

Is this possible?

I've tried below but haven't quite managed to achieve what I'm looking for.

Are there other simpler scripts that solve this, other than the one I've already tried below?

//LOAD ITEM
Item:
load
ITNO as %ArtNr,
ITDS as Itemdisc
;
SQL SELECT
ITNO, ITDS
FROM "PRD".dbo.Items
;

//LOAD STOCK
left Join (Item)
load
2ITNO as %ArtNr,
2STQT as Stock
;
SQL SELECT
2ITNO, 2STQT
FROM "PRD".dbo.Stocks
;

//LOAD FORECAST
Forecast:
load *
;
SQL SELECT *
FROM "Forecast".dbo.Forecasts
;

//LOAD SUPPLIER
Supplier:
load *
;
SQL SELECT *
FROM "Supplier".dbo.Suppliers
;

FinalData:
LOAD *,
'$(vToday)' as SnapshotDate
Resident ITEM;

Concatenate(FinalData)
LOAD *,
'$(vToday)' as SnapshotDate
Resident STOCK;

Concatenate(FinalData)
LOAD *,
'$(vToday)' as SnapshotDate
Resident FORECAST;

Concatenate(FinalData)
LOAD *,
'$(vToday)' as SnapshotDate
Resident Supplier;

// DROP TEMP TABLES
DROP Tables ITEM, STOCK, FORECAST, Supplier
;

// Save QVD as Date
STORE FinalData INTO C:\Users\TEST\QVD Test_$(vToday).qvd (qvd);

// DROP TABLE
DROP Table FinalData;

// LOAD ALL QVD
FOR EACH vFile IN FileList('C:\Users\TEST\QVD Test_$(vToday).qvd')
LOAD *
FROM [$(vFile)] (qvd);
NEXT vFile;

Re: Store tables into one QVD

Or — Tue, 13 May 2025 18:37:24 GMT

You would need to concatenate your tables into a single table in order to save them as one QVD. This approach is typically not a good idea.

Re: Store tables into one QVD

Bhushan_Mahajan — Wed, 14 May 2025 10:08:23 GMT

@andreasthisell Write Union in query then store in single qvd or run parallel task for each query store them in different QVDs and then concatenate those QVDs into single.

Re: Store tables into one QVD

marksouzacosta — Sat, 17 May 2025 01:08:21 GMT

Hi @andreasthisell,

If I get you right, you are doing data snapshots. There are multiple ways to do that and the approach you take depends on how big your data is and how frequently you run your Snapshots.

First of all, you cannot store multiple independent tables into a single QVD file. For that, you could try Parquet files.

Your approach of concatenating all tables into a single one is not recommended and there is a high change - if not 100% - of bad performance and wrong results. Concatenating is recommended when you need to combine different sets of records of tables with the same structure (fields).

For simple Snapshots, I would recommend something like this. I will use Suppliers as an example:

SET vSupplierQVD = 'C:\Users\TEST\Supplier.qvd';

Supplier:

SQL SELECT

'$(vToday)' as SnapshotDate

FROM

"Supplier".dbo.Suppliers

;

// Check if Supplier QVD exists

If Not IsNull(QvdCreateTime('$(vSupplierQVD)')) Then

// Load from existing QVD

Supplier:

LOAD * FROM [$(vSupplierQVD)](qvd);

End If

STORE Supplier INTO [$(vSupplierQVD)](qvd);

// Drop the table to free up memory

DROP TABLE Supplier;

In this approach, you save a table snapshot into a single QVD file. You could also save each table snapshot in a unique QVD file with the snapshot table suffix, like 'C:\Users\TEST\Supplier_$(vToday).qvd' but this can end in thousands of files that will slows down your load performance.

An advanced approach would be creating "Type 2 Slowly Changing Dimension" tables. You can research this topic and see if it fits your requirements.

Regards,

Mark Costa