Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need to calculate the difference between row count and previous row count that was overwritten later ?
For example , my data looks like this below
DateTime row count
01/28/2019 54
01/27/2019 43
When I refresh the dashboard, the row count for 01/28(which is today) gets updated, so the new count looks like this below
DateTime row count
01/28/2019 72
01/27/2019 43
How can I calculate the difference between the row count generated from the current run and row count generated from the previous run ?
Hi,
You may store data from each run into qvd (add or replace).
Then load this data and make calculation between stored and new rows.
It's just one qvd that gets overwritten again and again. So, how to store the previous value that was already updated ?
If you don't need a history then it will update on each reload.
e.g.
IF IsNull(filetime('RowCountPrior.qvd ')) THEN // Necessary for the first run when no qvd generated
RowCountPrior:
LOAD * Inline [
DateTime as DateTimePrior,
RowCount as RowCountPrior
FROM source;
Store RowCountPrior into RowCountPrior.qvd (qvd);
Drop Table RowCountPrior;
RowCountTable:
LOAD
DateTime ,
RowCount,
'0' as RowCountDiff
FROM source;
ELSE
Map_PriorRowCount:
Mapping Load
DateTimePrior,
RowCountPrior
FROM RowCountPrior.qvd (qvd);
RowCountTable:
LOAD
DateTime ,
RowCount,
RowCount - ApplyMap('Map_PriorRowCount', DateTime ) as RowCountDiff
FROM source;
RowCountPrior:
LOAD * Inline [
DateTime as DateTimePrior,
RowCount as RowCountPrior
FROM source;
Store RowCountPrior into RowCountPrior.qvd (qvd);
Drop Table RowCountPrior;
END IF
I think you may skip the last step (RowCountPrior) and store into qvd from RowCountTable
It depends on requirements..
Bob, did the final post get you a working solution for your use case? If so, do not forget to return to your thread and on that post, use the Accept as Solution button to mark it to give credit to the poster for the help and to let other Members know that did work for you. The only other idea I had was to perhaps use variables, but the trick is if you are loading things an inconsistent number of times, that would not work, believe the other method proposed would be the way to go in that case.
For future reference, you can also check the Design Blog area of the Community to see if you can find any ideas out there:
https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog
Regards,
Brett