topic Re: Get Maximum record in App Development

Get Maximum record

manojkulkarni — Mon, 31 May 2021 10:21:50 GMT

Hi All,

How to get the maximum record data from the given list in the script ? Below is the sample data and expected result.

Sample Data:

variable	timestmp	value	Date
53153540471	00:02:31	10	25-05-2021
53153541051	00:02:31	14	25-05-2021
53153540471	00:06:07	15	25-05-2021
53153541051	00:06:07	9	25-05-2021
53153540471	00:11:06	25	25-05-2021
53153541051	00:11:06	120	25-05-2021
53153540471	00:16:06	0	25-05-2021
53153541051	00:16:06	11	25-05-2021

Expected Result:

variable	timestmp	value	Date
53153541051	00:02:31	14	25-05-2021
53153540471	00:06:07	15	25-05-2021
53153541051	00:11:06	120	25-05-2021
53153541051	00:16:06	11	25-05-2021

Re: Get Maximum record

JuanGerardo — Mon, 31 May 2021 10:38:33 GMT

Hi @manojkulkarni, I suggest to use the Max and FirstSortedValue functions for aggregating that table:

Data: Load * Inline [ variable timestmp value Date 53153540471 00:02:31 10 25-05-2021 53153541051 00:02:31 14 25-05-2021 53153540471 00:06:07 15 25-05-2021 53153541051 00:06:07 9 25-05-2021 53153540471 00:11:06 25 25-05-2021 53153541051 00:11:06 120 25-05-2021 53153540471 00:16:06 0 25-05-2021 53153541051 00:16:06 11 25-05-2021 ] (delimiter is '\t'); DataMax: NoConcatenate Load FirstSortedValue(variable, value) AS variable, Date, timestmp, Max(value) AS value Resident Data Group By Date, timestmp ; Drop Table Data;

Re: Get Maximum record

manojkulkarni — Mon, 31 May 2021 10:50:52 GMT

Thanks for your Quick reply. Above script is not returning the expected result

Re: Get Maximum record

JuanGerardo — Mon, 31 May 2021 11:00:31 GMT

OK, I see the mistake. You have to use -value instead of value in the firstsortedvalue:

Data: Load * Inline [ variable timestmp value Date 53153540471 00:02:31 10 25-05-2021 53153541051 00:02:31 14 25-05-2021 53153540471 00:06:07 15 25-05-2021 53153541051 00:06:07 9 25-05-2021 53153540471 00:11:06 25 25-05-2021 53153541051 00:11:06 120 25-05-2021 53153540471 00:16:06 0 25-05-2021 53153541051 00:16:06 11 25-05-2021 ] (delimiter is '\t'); DataMax: NoConcatenate Load FirstSortedValue(variable, -value) AS variable, Date, timestmp, Max(value) AS value Resident Data Group By Date, timestmp ; Drop Table Data;