Skip to Content
0
Former Member
Nov 21, 2012 at 08:34 PM

BEx Query Designer - Filtering by a date and returning data based on a record's max date for a dataset

252 Views

I am currently having a issue and was looking to obtain suggestions on how to fix my problem. I am working with financial data and the records I am loading only have changed records when there is daily activity.

I will illustrate an example that will help to explain this:

Fund A Fund B Fund C Effective Date

Units 50 50 50 2012/01/01

70 55 2012/02/28

50 2012/03/30

90 60 2012/04/30

When I query I want to query the effective date of 2012/04/30 and have the units returned the following way:

Fund A - 90 units

Fund B - 50 units

Fund C - 60 units

The source system supplying the data though will not create a duplicate record if nothing has changed (no activity since the last date of activity). So when I do an effective date query on a single date I will not get any information returned for Fund B. I also tried using a range but that gives me a summation of values as well as I have to go through many upon many of columns to decipher what the final balance is for that fund.

My initial thoughts were to either set the unit key figure to last value for a calculation so I can get the actual number I desire. The drawback with this is my query will take forever to run because it will return every date of change for a fund and there are thousands of funds. My other thought was to use an aggregation of effective date and set it to max for all key figures that way it would look up the last date information. I am not sure that will work though.

Any suggestions would be much appreciated.