Question Re: Min Value and Max Value from Same Dataset in Technology Q&A

Min Value and Max Value from Same Dataset

Former Member — Wed, 10 Apr 2019 18:45:48 GMT

I am attempting to extract data from a database that has an error in it. I can't resolve the error (it's a "design feature"), so i have to try to query around it. Here's how it is stored.

Record ID | Create Date | Update Date | Record Status
123       | 05/01/2018  | 05/01/2018  | Active
123       | 05/08/2018  | 05/08/2018  | Active
123       | 05/15/2018  | 05/15/2018  | Closed
123       | 05/22/2018  | 05/22/2018  | Closed
456       | 06/02/2018  | 06/02/2018  | Pending
456       | 06/09/2018  | 06/09/2018  | Active
456       | 06/16/2018  | 06/16/2018  | Active
456       | 06/23/2018  | 06/23/2018  | Suspended

And so on. As you can see, the Create Date and Update Date values match on each row. The Create Date value is supposed to be the date the Record ID was initially created, but it's actually being captured as the date the Record ID update was created.

What I need is a report that brings me a single row per Record ID that shows me the minimum Create Date and the maximum Update Date, so that the result looks something like this:

Record ID | Create Date | Update Date | Record Status
123       | 05/01/2018  | 05/22/2018  | Closed
456       | 06/02/2018  | 06/23/2018  | Suspended

I've tried adding MIN and MAX variables, but that doesn't seem to have any effect on the data.

I'm relatively new to Business Objects (WEBI), though I think I'm picking up its concepts quickly. What am I missing here?

Re: Min Value and Max Value from Same Dataset

developerone — Wed, 10 Apr 2019 19:43:00 GMT

Store the MIN and MAX dates in two variables. Use them as a condition to pull the record in your query filters. Please see if this works.

Re: Min Value and Max Value from Same Dataset

amitrathi239 — Thu, 11 Apr 2019 15:57:29 GMT

follow below steps.

Create variable Show/Hide=If([Update Date])=Max([Update Date]) In ([ID]) Then "Show" Else "Hide"

Apply table level filter on Show/Hide where equal to "Show".After that you will get one row for each ID.

Next step is use below formula in Create Date column to display min date.

=NoFilter(Min([Create Date]) In ([ID]))

Re: Min Value and Max Value from Same Dataset

Former Member — Thu, 11 Apr 2019 17:16:29 GMT

Tried it. No luck. The Create Date and the Update Date--including the columns with the MIN and MAX variables--still match on all rows.

Re: Min Value and Max Value from Same Dataset

Former Member — Thu, 11 Apr 2019 17:35:41 GMT

That seems to be working. I'm still getting some duplicate lines, but I think I know what's causing those. I think I'll work your solution into a more complex query to see if it works in a broader application.