Prior/Previous data

[tom_tierney]

All,

I am extracting data from a wide/long database view. (600/4M). But  for this example assume 4 fields.

Date, Cust ID, Amount1, Amount2

Date	Cust ID	Amount1	Amount2	PrevAmount1	PrevAmount2	Other
20140131	001	10	20
20140131	002	12	22
20140228	001	14	24	10	20
20140228	002	16	26	12	22

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)

Any thoughts?

T

[maxgro]

what's the meaning of

"Peek/Previous are non runners because the sort order is not fixed."

?

PrevAmount1 of 20140228  001

is Amount1 of same Cust ID, previous date

ordering by Cust ID, Date

or I'm missing something?

[tom_tierney]

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.