Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
@swuehl Any thoughts please
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;
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.