Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

tom_tierney
Not applicable

Prior/Previous data

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

DateCust IDAmount1Amount2PrevAmount1PrevAmount2Other
201401310011020
201401310021222
2014022800114241020
2014022800216261222

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

2 Replies
maxgro
Not applicable

Re: Prior/Previous data

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
Not applicable

Re: Prior/Previous data

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.