Cloud:What is the best way to store previous years data for an application and merge with current queried year data

[dwighttrumbower]

Moved to the cloud and would like to keep previous years data stored there and just query sql through the gateway for current year. Then combine the data for the application. 

What is the best recommended way in the cloud to do this?

Thanks

[marksouzacosta]

Hi @dwighttrumbower,

There are multiple ways to solve this problem.

One solution is to move your historical data to Qlik Cloud, simple uploading your historical data and naming it accordingly: Example: sales_historical.qvd.

Next, your regular ETL App will load your data from SQL and store as sales.qvd (depending on your data you can do data partitions like sales_2025.qvd, sales_2024.qvd or even sales_202512.qvd, sales_202511.qvd, etc).

Your Dashboard Layer, or even a Layer 2 QVD Builder App, will just combine those records: Example:
[Sales]:
LOAD * FROM [$(yourLibraryAndFolder)sales_*.qvd](qvd);

Regards,

Mark Costa

[dwighttrumbower]

Do I have to build some type of ETL app to do this?

[marksouzacosta]

If it is a one-time thing, you don't need to do any ETL app. Just upload the QVD file straight to your Qlik Cloud Tenant:

Regards,

Mark Costa

[dwighttrumbower]

Well, one would have to create the QVD.

[AustinSpivey · Accepted solution]

I agree with @marksouzacosta that creating QVD partitions is a good strategy. Here's a simple step-by-step of how you can do this:

0. Assumptions

• I assume you already have a space set up to hold your data, scripts, and apps.

1. Create a new Sales subfolder in your space data files.

• Go to the Qlik Analytics Catalog page (https://YOUR-TENANT.us.qlikcloud.com/analytics/catalog).
• Choose your space from the Spaces dropdown at the top of the page. I'm using my Personal space in this example.
• On the right side of the page, click on Space settings and then select Data files:

2. Add a Sales folder.

• Click on the Add folder button:

• Add a folder called Sales (or whatever you'd like):

3. Create your QVD generator script.

• Go back to the Catalog page, click on the green Create new button, and then select the Script option:

• Name the new script whatever makes sense:

• Open the new script.
• When it opens, click the Editor button at the top-left of the page.
• When the Editor view appears, create a new script section called Load and Store (or whatever).
• Copy, paste, and adapt this script:

// Initial QVD creation for previous years' data:

Lib Connect To 'Sales Database';

//// NOTE 1 ////
[Sales Years]:
Select Distinct
    Date_Part('year', "sales_date") as "sales_year"
From "sales1"."sales";

//// NOTE 2 ////
Let vCurrentYear = Year(Today());
Trace #### vCurrentYear = $(vCurrentYear);

//// NOTE 3 ////
For Each vSalesYear In FieldValueList('sales_year')
    //// NOTE 4 ////
    If vSalesYear <> vCurrentYear Then
        //// NOTE 5 ////
        Let vNextSalesYear = vSalesYear + 1;
        
        //// NOTE 6 ////
        [sales_$(vSalesYear)]:
        Select
            *
        From "sales1"."sales"
          Where "sales_date" >= '$(vSalesYear)-01-01'
            And "sales_date" < '$(vNextSalesYear)-01-01'
        ;
        
        //// NOTE 7 ////
        Store [sales_$(vSalesYear)] Into [lib://DataFiles/Sales/sales_$(vSalesYear).qvd](QVD);
        //// NOTE 8 ////
        Drop Table [sales_$(vSalesYear)];
    End If
Next vSalesYear

Drop Table [Sales Years];

Here are some notes on that script:

• Note 1: This chunk here runs a SQL Select statement against the sales table to get the distinct years based on the sales_date column. I do this to get the years that I am going to partition into separate QVDs.
• Note 2: Here I get the current year and set that to a variable called vCurrentYear. I then trace it out so that I can check it in the logs to make sure I did it correctly. Better to use a variable than hardcode anything.
• Note 3: Here I start a For Each loop that will iterate through each year that I pulled in that first step. Each iteration will set the year to the variable called vSalesYear.
• Note 4: Here I check to see if vSalesYear is the same as the current year variable. I want to skip this process if it's the current year, since we want to pull current year data from the database directly.
• Note 5: Here I just set a variable called vNextSalesYear to vSalesYear + 1 so that I can use it in the Where clause in the next step.
• Note 6: Here's where I hit the sales database and query my sales table where sales_date is in the year that vSalesYear is currently set to. Notice that my table is named dynamically using the vSalesYear variable -- this is not necessary, but can be nice for debugging and such.
• Note 7: At this point, I store the table to a QVD in the Sales folder I created earlier in my space data files.
• Note 8: Here we drop the table, as we no longer need it in this script. This is not necessary in scripts since they don't keep a data model like apps, but it's still good practice.

4. Run the script successfully once.

5. Create or open your sales app.

6. Go to the Data Load Editor and add this to a new script section:

Let vCurrentYear = Year(Today());
Trace #### vCurrentYear = $(vCurrentYear);

[sales]:
Load
    *
From [lib://DataFiles/Sales/sales_*.qvd]
(QVD);


Lib Connect To 'Sales Database';

Concatenate ([sales])
Select
    *
From "sales1"."sales"
  Where "sales_date" >= '$(vCurrentYear)-01-01'
;

This script loads all of the sales QVDs we stored in the previous step by utilizing the asterisk as a wildcard. We then pull all sales data from our sales table where the sales_data column is in the current year, which is immediately concatenated into the table we loaded from our QVDs. The end result is all of our sales data.

Some notes

• Going forward, you may want to schedule your script to run yearly to store down the previous year's sales data to a QVD.
• I simplified a lot, including the fact that I'm only storing one sales table -- in real life, you'd probably have several fact tables that you'd want to partition to QVDs like we did here. That'd still be very doable using this script as a framework, but it could be optimized with subroutines and would need some extra joins and such, of course.
• I didn't do any transformations or preceding loads in my example, but those steps could affect how you handle concatenating the tables in that last step.