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!
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();
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.
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. :
$(vMyTableStart) as Table1LoadStartedAt,
$(vMyTableEnd) as Table1LoadStoppedAt,
$(vMyTableCount) as Table1NumberOfRecords,
$(vNumKeyValues) as Table1NumberOfKeyValues,
$(vMyOtherTableStart) as Table2LoadStarteAt,
For more help on the NoOfRows function please refer to the online documentation which shows other table functions: Table functions ‒ Qlik Sense