Append daily records for historical tracking

[#1]

Hi,

My goal is to create a historical  trend dashboard.  Currently, no data exists for the number of records loaded for previous dates in a data source.   The data in the table is deleted every day and reloaded with a new data set.  What is the best method to use to append a record that would contain the date and the number of records loaded for each day?  The new daily record would be the count of records from daily refresh from the Qlik dashboard.

Has anyone had experience with this scenario and what was your solution?  Thanks!

[Gysbert_Wassenaar]

The data in the table is deleted every day and reloaded with a new data set.

That's the source table of your data source you're talking about? If so it's pretty straightforward.

// 1. Load today's data

MyTable:

LOAD *, Today() as Date;

SQL SELECT * FROM ...source table...;

// 2. If a qvd file with historical data already exists then append that data to the table

IF NOT IsNull(QvdCreateTime('HistoricalData.qvd')) THEN

     CONCATENATE (MyTable)

     LOAD * FROM HistoricalData.qvd (qvd);

ENDIF

// 3. Store the table into a qvd file

STORE MyTable INTO HistoricalData.qvd (qvd);

If you want counts per date you can create a straight table with Date as dimension and count(Date) as expression to get a record count per date.

If all you want is the counts then you can load the daily data into a table and use the NoOfRows function to retrieve the record count:

// 1. Load today's data

MyTable:

SQL SELECT * FROM ...source table...;

// 2. Get the record count:

LET vCount = NoOfRows('MyTable');

RecordCounts:

LOAD Today() as Date, $(vCount) as RecordCount AutoGenerate 1 ;

// 3. Append previous record counts

IF NOT IsNull(QvdCreateTime('HistoricalRecordCounts.qvd')) THEN

     CONCATENATE (RecordCounts)

     LOAD * FROM HistoricalRecordCounts.qvd (qvd);

ENDIF

// 4. Store the table into a qvd file

STORE RecordCounts INTO HistoricalRecordCounts.qvd (qvd);

[Dalton_Ruer]

gwassenaar‌ answer is on point ... as always. No wonder he's a Qlik Community MVP and a Qlik Luminary.

I'm only responding as this is the perfect opportunity to elaborate a little as this kind of metadata is asked about frequently and others are interested in a few other things that I will mention in case it sparks your interest. In Gysbert's section #2 above he loads the number of records. Let's say we also want to record how long it takes to do the loading so we can track any changes as time goes on. All we need are two additional variables that will record exactly when the load starts and when the load stops.

// 1. Load today's data

LET vMyTableStart = Now();

MyTable:

SQL SELECT * FROM ...source table...;

LET vMyTableEnd = Now();

Then lets say in addition to knowing that 1,293,304 rows were added we want to know how many unique values there are for some key field(s). We can create another variable (or multiple variables for multiple fields), that uses the FieldValueCount function.

// 2. Get the record count:

LET vMyTableCount = NoOfRows('MyTable');

LET vNumKeyValues = FieldValueCount('MyKeyFieldFromMyTable');

So many examples online use the keyword SET for variables, so why did Gysbert use LET instead? SET assigns the formula to the variable as in "NoOfRows('MyTable') and then that is what would be output to the file. LET instead actually executes and assigns the value of the formula so that you get the # of rows, or the time or the data.

The part of Gysbert's code that might be confusing is how he builds the RecordCounts table.

RecordCounts:

LOAD Today() as Date, $(vCount) as RecordCount AutoGenerate 1 ;

The AutoGenerate keyword is a really neat feature that replaces loading data INLINE, or FROM tables or from RESIDENT tables. It "automatically generates values." In this case he is just saying 1 so that 1 row would be generated. This blog post has some great examples that show other ways to take advantage of that feature. Autogenerate Your Data  The key thing to understand is that the values he is creating in the "RecordCounts" table could be anything, they aren't tied to the AutoGenerate keyword in anyway.

So you can easily do something like the following where you output any/all values and you can also record the values for multiple table loads if there is more than 1 that you are interested in. :

RecordCounts:

LOAD

     $(vMyTableStart) as Table1LoadStartedAt,

     $(vMyTableEnd) as Table1LoadStoppedAt,

     $(vMyTableCount) as Table1NumberOfRecords,

     $(vNumKeyValues) as Table1NumberOfKeyValues,

     $(vMyOtherTableStart) as Table2LoadStarteAt,

....

AutoGenerate 1;

For more help on the NoOfRows function please refer to the online documentation which shows other table functions: Table functions ‒ Qlik Sense

Help is also available for the FieldValueCount function at: https://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/Scripting/CounterFunctions/field...