topic Re: Min and Max Dates in App Development

Min and Max Dates

melissapluke — Fri, 02 Apr 2021 08:10:15 GMT

I have a table loaded like this:

Load
Item,
Start Date,
End Date,
Rate
From Table;

For one single item it looks like this:

I want to keep only the min and max start/end dates IF THE DATES ARE SEQUENTIAL.

I want the results to look like this:

1/1/2015	6/30/2015	$0.20
7/1/2015	9/30/2015	$0.21
10/1/2015	3/31/2016	$0.20
4/1/2016	6/30/2016	$0.21
7/1/2016	12/31/2016	$0.20
1/1/2017	3/31/2017	$0.24

However, if I try something like this:

Load
Item,
Min(Start Date) as Start Date,
Max(End Date) as End Date,
Rate
From Table
Group by Item, Rate;

I get the min and max of ALL the dates, not only the dates that are in sequential order. I do not want the Max End Date of a line to overlap with the next min date.

Any ideas on what to try?

Re: Min and Max Dates

StarinieriG — Tue, 05 Nov 2019 09:00:27 GMT

Hi,

try to create a new field like this:

RowNo () AS NR

then order by that and create a new field, so you could use it in group by:

If(rate=Previous(rate),Peek('NR'),NR)

Re: Min and Max Dates

sunny_talwar — Tue, 05 Nov 2019 13:02:55 GMT

Here is one way you can do this

Table: LOAD * INLINE [ Start Date, End Date, Rate 01/01/2015, 03/31/2015, 0.20 04/01/2015, 06/30/2015, 0.20 07/01/2015, 09/30/2015, 0.21 10/01/2015, 12/31/2015, 0.20 01/01/2016, 03/31/2016, 0.20 04/01/2016, 06/30/2016, 0.21 07/01/2016, 09/30/2016, 0.20 10/01/2016, 12/31/2016, 0.20 01/01/2017, 03/31/2017, 0.24 ]; FinalTable: LOAD *, If(Rate = Previous(Rate), Peek('Start Date New'), [Start Date]) as [Start Date New] Resident Table Order By [Start Date]; Right Join (FinalTable) LOAD [Start Date New], Rate, Max([End Date]) as [End Date] Resident FinalTable Group By [Start Date New], Rate; DROP Table Table; DROP Field [Start Date]; RENAME Field [Start Date New] to [Start Date];

Re: Min and Max Dates

melissapluke — Tue, 05 Nov 2019 13:05:33 GMT

Thanks, Sunny. That will work!