I am extracting data from a wide/long database view. (600/4M). But for this example assume 4 fields.
Date, Cust ID, Amount1, Amount2
The desired result is shown above. I would like the previous months values (for approx. 10 fields) to be available in the current months dataset. PrevAmount1 and PrevAmount2 in the example above.
Ideally I would like to achieve this in the LOAD statement and not via set analysis. (I have looked at Oleg Troyansky's Current Month/Previous Month post but this uses set analysis albeit in a very efficient way) http://community.qlik.com/message/475041#475041
I have also tried mapping loads but the data set is too large so load times are too long.
Peek/Previous are non runners because the sort order is not fixed.
The PrevAmount1 & PrevAmount2 fields need to be valid when the selected date is changed. (Assume 4 years of data/100K records/rows per month)
My example was a very simplified model. In reality I don't have a customer ID. Instead a staggered hierarchy. So for example I might have Deal level data with 30 attributes and 20 values/amounts. Then Facility level data with 35 attributes and 25 values/amounts. Next Legal Entity level data etc. Usually, but not always, values can be summed up through the hierarchy. There are 10 levels in the hierarchy and being staggered, for a given record, a value/amount might only exist at the top 2-3 levels. So the required sort is just too expensive in terms of the time it adds to the load process. Also, all the data has to be loaded every time because historic data is often enriched with PV and other movements. So little opportunity to cache.
I think what I was after was a general view and suggestions as to how others have solved this type of problem. Your answer was helpful and I have been looking through the forums recently for other ideas. I am leaning towards a SQL solution so that the historic data is delivered in the view.