topic Re: how to get the largest value in the same table by comparing two rows ? in QlikView

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

nishanthi_8 — Wed, 25 Nov 2020 16:16:04 GMT

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 ?

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

MK_QSL — Fri, 30 Dec 2016 10:22:44 GMT

What is the final output you are looking for?

Can you provide it here?

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

nishanthi_8 — Fri, 30 Dec 2016 10:33:15 GMT

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

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

sunny_talwar — Fri, 30 Dec 2016 10:39:33 GMT

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;

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

MK_QSL — Fri, 30 Dec 2016 10:41:04 GMT

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;

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

nishanthi_8 — Fri, 30 Dec 2016 10:48:59 GMT

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

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

MK_QSL — Fri, 30 Dec 2016 10:56:40 GMT

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;