topic Re: Price from date columns in QlikView

Price from date columns

v_jaideep — Fri, 02 Apr 2021 08:10:32 GMT

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

Re: Price from date columns

v_jaideep — Thu, 01 Apr 2021 19:29:31 GMT

@swuehl Any thoughts please

Re: Price from date columns

swuehl — Thu, 22 Apr 2021 21:25:19 GMT

Are you sure that your expected data is correct?

How did you calculate

5.2

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;

Re: Price from date columns

v_jaideep — Fri, 23 Apr 2021 11:04:19 GMT

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.