Price from date columns

[v_jaideep]

Hello,

I have start_date,product,size and price. I need to get current_price when date is minimum based on product,size and get old_price when the date is maximum based on product,size. Current Price and Old Price should be in the same column.Also tried using Firstsortedvalue(price,-start date).. this just gives  blank(-)

Below is current data and expected data.

Current Data:

product	start date	size	price	type
A	3/21/2020	20	1000	MF
A	8/22/2019	20	120	MF
A	11/1/2019	20	50	MF
A	3/13/2016	15	50	RT
A	12/17/2012	15	20	RT
B	3/1/2019	12	5.2	MF
B	1/1/2021	12	520	AS
B	10/28/2019	11	900	AS
B	3/28/2020	11	22	MF
C	10/28/2019	11	45	AS
C	4/2/2019	15	2000	RT
C	3/2/2020	15	1200	RT

Expected Date:

product	size	OLD price	Current Price	type
A	20	120	1000	MF
A	15	12	50	RT
B	12	5.2	22	MF
B	11	900	520	AS
C	15	2000	1200	RT
C	11	45	45	AS

[v_jaideep]

@swuehl  Any thoughts please

[swuehl]

Are you sure that your expected data is correct?

How did you calculate 

B

12

5.2

22

MF

there is no price 22 for the combination of product and size.

In general, a group by LOAD in combination with FirstSortedValue Aggregation should work. Double check, that your start_date field is numeric / dual.

For example, do it similar to this:

Set DateFormat ='M/D/YYYY';

INPUT:
LOAD * INLINE [
product	start date	size	price	type
A	3/21/2020	20	1000	MF
A	8/22/2019	20	120	MF
A	11/1/2019	20	50	MF
A	3/13/2016	15	50	RT
A	12/17/2012	15	20	RT
B	3/1/2019	12	5.2	MF
B	1/1/2021	12	520	AS
B	10/28/2019	11	900	AS
B	3/28/2020	11	22	MF
C	10/28/2019	11	45	AS
C	4/2/2019	15	2000	RT
C	3/2/2020	15	1200	RT
] (delimiter is '\t');

Result:
LOAD product, size, 
	FirstSortedValue(price,[start date]) as [OLD price],
	FirstSortedValue(price,-[start date]) as [CURRENT price],
	FirstSortedValue(type,-[start date]) as [type]
Resident INPUT
Group by product, size;

DROP Table INPUT;

[v_jaideep]

Thanks swuehl for your valuable time, Group by should be on product and type...I was able to get the data as expected using firstsortedvalue(). I was not able to get the expected data earlier because I was referring incorrect date column.