As part of a migration project I have been tasked with delivering the 'Take On' balances. In essence this means producing a output which consists of an ACCID. Period and a couple of values. The trick is to only show the values for the LAST period for each account.
The process I have followed is to load the source data, then doing a resident load, sorting the data and via the Peek function producing a temporary field with a load or reject description. In production, because the data is so big I store to a qvd then reload only where the temp field is 'Load'.
This worked perfectly but I am not sure if it technically correct from a QlikView perspective!. Please could anyone advise if there is / was a better way, bearing in mind the source was many millions of records.
The attached example is a simplified version of what was done. In the example there are only 2 accounts with 22 records and only returns 2 records required, 1 per account.
A star for QlikView. The original tool that was being used had to be stopped, after 2 days because the server was running out of resources. Whether it was the tool or bad scripting I cannot tell. From the time of getting the request to delivering the csv took a morning to do.