Filtering aggregates

Hello I'm trying to filter my aggregate so that my list shows each truck stops 'todays price' in effective date. 


In the fuel price data, there is more than one instance of the same truck stop id signifying different days fuel prices as shown.


 


I need to just grab one instance of the truck stop id record where the date is todays date. If there's not todays date, as in truck stop id 1, I need to display "Todays date not available"

This is my aggregate and the data tables I'm joining.



So again, I need my aggregate to display each truck stops id with the effective date attribute being todays date and if there is a record without a effectivedate that is today, then effective date needs to say "Todays price not available". 

Thanks

Hi Jeffrey,

Didn't understand your problem. but if the problem is to state that today price is not available you should do that in the UI. Check if the FuelPrice.Id = NullIdentifier() and if yes show the message that you want

Regards,

Marcelo

Solution

Ok if I read your question right then try the following

1) Do a Group by on the fields you want to display (excluding the price). 

2) Decide how you want to handle multiple prices for one stop on the same day, show the highest or the lowest.

3) For the Highest do a "MAX" on the price, for the lowest do a MIN

If you need to get only the last price then that gets a little more tricky and you will need to go to an advanced SQL query.


Then for the no price do as Marcelo suggested and use an if statement in the UI to display a message or the price, probably where the price = 0 though as you don't want the FulePrice.Id in your group by and I thing MIN & MAX may return 0 instead of nullidentifier() but try both if it doesn't work.


Solution