Get Maximum record

[manojkulkarni]

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

[JuanGerardo]

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;

JG

[manojkulkarni]

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

[JuanGerardo · Accepted solution]

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;

JG