Ask for Expression

[Anonymous]

Hi,

I have a data like this:

ITEM_ID	PART_ID	DATE	AVG_RH		Calculation
P	P1	12-Feb-2018	7288.21		9505.7 - 7288.21
P	P1	14-Feb-2018	7288.21		9505.7 - 7288.21
P	P1	06-Jun-2018	8726.46		9505.7 - 7288.21
P	P1	13-Aug-2018	9505.7		9505.7 - 7288.21
P	P2	05-Jan-2018	6732.4		7288.21-6732.4
P	P2	22-Jan-2018	7060.95		7288.21-6732.4
P	P2	29-Jan-2018	7162.55		7288.21-6732.4
P	P2	05-Feb-2018	7267.1		7288.21-6732.4
P	P2	12-Feb-2018	7288.21		7288.21-6732.4

I want to get AVG_RH value in max(DATE) and Min(DATE) for each ITEM_ID and PART_ID.

For example ITEM_ID = P and PART_ID = P1

I want to get AVG RH in MAX DATE (13-Aug-2018) and MIN DATE(12-FEB-2018) so the result must be in Calculation column above.

Can somebody help me.

Thanks

[andrey_krylov]

Try this

Only(TOTAL <ITEM_ID, PART_ID> Aggr(If(Max(TOTAL <ITEM_ID, PART_ID> DATE) = DATE,  AVG_RH), ITEM_ID, PART_ID, DATE)) & ' - ' & Only(TOTAL <ITEM_ID, PART_ID> Aggr(If(Min(TOTAL <ITEM_ID, PART_ID> DATE) = DATE,  AVG_RH), ITEM_ID, PART_ID, DATE))