Only refresh changed rows possible with Scheduled Refresh
Need some help once more since I'm struggeling at one of my projects.
The situation is as follows. We have a big amount of sales and purchase order lines combined in to 1 table. All data we get from SQL Database. We currently do not have a DateTimeLastModified field on the rows but this could be added if it is a solution.
We use Import and have 8 scheduled refreshes during working hours.
This takes up alot of resources however with only a handful of rows changing between each refresh. I've looked into the possibility of using scheduled refresh functionality but I'm not 100% sure that this works as a solution in our case. Since all the rows in our dataset are subject to changes and never 'final'. It seems that with scheduled refresh you only set a timeframe in which records should be refreshed. So if I set RangeStard to 9-4-2021 and RangeEnd to 10-4-2021, only these records are refreshed:
Or could we just add the DateTimeLastModified to the rows, then set up RangeStart as minimum date and RangeEnd as maximum date and set the Detect changes field as the DateTimeLastModified?
Incremental refresh is not entirely suited for what you are trying to achieve. What you are looking for is differential refresh (what Qlik calls CDC).
Absolutely DO add the DateLastModified column to your data, it is extremely useful.
BUT! You cannot specify the same field for RangeStart/RangeEnd and for Change detection. These have to be fed from separate fields.
What we ended up doing was to use a hybrid approach where we would fetch the data in reasonable chunks (for example fiscal quarter, or "transactions created in the last three months" , with the assumption that the data would have calmed down after that period, and then flush and fill only these chunks as needed.
We do use incremental refresh, but our RangeStart/RangeEnd is based on the chunk dates, not the data dates (we don't have the luxury of the DateLastModified field in our data)