how to get the largest value in the same table by comparing two rows ?

[nishanthi_8]

Here is the sample table :

Object_Name	Time_Stamp	Analog_value
GRID VALUE	02-12-2016 16:01		77741
GRID VALUE	01-12-2016 17:51		78522
GRID VALUE	01-12-2016 16:49		78521
GRID VALUE	01-12-2016 15:48		78520
GRID VALUE	01-12-2016 14:49		78519
GRID VALUE	01-12-2016 13:48		78518
GRID VALUE	01-12-2016 12:51		78517
GRID VALUE	30-11-2016 00:15		78247

here I need to compare each and every row with the time stamp and if it is max should fetch the details. For example , for 01-12-2016

I should compare 78518 > 78517 and then  it should return 78518 and then again it should compare with 78519 > 78518. Any solution to achieve this ?

[MK_QSL]

What is the final output you are looking for?

Can you provide it here?

[nishanthi_8]

GRID VALUE

02-12-2016 16:01

77741

GRID VALUE

01-12-2016 17:51

78522

GRID VALUE30-11-2016 00:15     78247

I know this can be done with max(Analog_value). But I dont want that as these need to be checked with each and every row and get the max along with the date

[sunny_talwar]

Are you looking for this?

Table:

LOAD *,

  Date(Floor(Time_Stamp)) as Date

INLINE [

    Object_Name,  Time_Stamp,    Analog_value

    GRID VALUE, 02-12-2016 16:01, 77741

    GRID VALUE, 01-12-2016 17:51, 78522

    GRID VALUE, 01-12-2016 16:49, 78521

    GRID VALUE, 01-12-2016 15:48, 78520

    GRID VALUE, 01-12-2016 14:49, 78519

    GRID VALUE, 01-12-2016 13:48, 78518

    GRID VALUE, 01-12-2016 12:51, 78517

    GRID VALUE, 30-11-2016 00:15, 78247

];

Left Join (Table)

LOAD Object_Name,

  Date,

  Max(Analog_value) as Max_Analog_value

Resident Table

Group By Object_Name, Date;

[MK_QSL]

Data:

Load

  Object_Name,

  TimeStamp#(Time_Stamp,'DD-MM-YYYY hh:mm') as Time_Stamp,

  Date(Floor(TimeStamp#(Time_Stamp,'DD-MM-YYYY hh:mm')),'DD-MM-YYYY') as Date,

  Analog_value

Inline

[

  Object_Name, Time_Stamp, Analog_value

  GRID VALUE, 02-12-2016 16:01, 77741

  GRID VALUE, 01-12-2016 17:51, 78522

  GRID VALUE, 01-12-2016 16:49, 78521

  GRID VALUE, 01-12-2016 15:48, 78520

  GRID VALUE, 01-12-2016 14:49, 78519

  GRID VALUE, 01-12-2016 13:48, 78518

  GRID VALUE, 01-12-2016 12:51, 78517

  GRID VALUE, 30-11-2016 00:15, 78247

];

Left Join (Data)

Load

  Object_Name,

  Date,

  Max(Analog_value) as MaxAnalogValue

Resident Data

Group By Object_Name,Date;

[nishanthi_8]

no this is straight forward that is using max function. What I need is to compare each row and determine the maximum value

[MK_QSL]

Data:

Load

  Object_Name,

  TimeStamp#(Time_Stamp,'DD-MM-YYYY hh:mm') as Time_Stamp,

  Date(Floor(TimeStamp#(Time_Stamp,'DD-MM-YYYY hh:mm')),'DD-MM-YYYY') as Date,

  Object_Name & Date(Floor(TimeStamp#(Time_Stamp,'DD-MM-YYYY hh:mm')),'DD-MM-YYYY') as ObjectDateKey,

  Analog_value

Inline

[

  Object_Name, Time_Stamp, Analog_value

  GRID VALUE, 02-12-2016 16:01, 77741

  GRID VALUE, 01-12-2016 17:51, 78522

  GRID VALUE, 01-12-2016 16:49, 78521

  GRID VALUE, 01-12-2016 15:48, 78520

  GRID VALUE, 01-12-2016 14:49, 78519

  GRID VALUE, 01-12-2016 13:48, 78518

  GRID VALUE, 01-12-2016 12:51, 78517

  GRID VALUE, 30-11-2016 00:15, 78247

];

Final:

Load

  RowNo() as ID,

  Object_Name,

  Time_Stamp,

  Date,

  Analog_value,

  IF(ObjectDateKey <> Previous(ObjectDateKey),

  Analog_value,

  RangeMax(Analog_value,Peek('MaxAnalogValue'))) as MaxAnalogValue

Resident Data

Order By ObjectDateKey, Time_Stamp

;

Drop Table Data;