topic Re: Last updated Price for all Products based on date selection in QlikView

Last updated Price for all Products based on date selection

prathapG — Sun, 16 Feb 2020 13:43:48 GMT

Hi Team,

Please help me on achieving this logic

I want to show Product & Last updated Price in Straight table based on date selection in listbox.

Data Table:

Product	Date	Price
Samsung	01/01/2020	15000
Apple	02/01/2020	45000
Nokia	03/01/2020	8000
Motorola	04/01/2020	11000
Samsung	11/01/2020	17000
Apple	12/01/2020	47000
Nokia	13/01/2020	9000
Motorola	14/01/2020	11500
Samsung	20/01/2020	19000
Apple	21/01/2020	43000
Nokia	22/01/2020	7500
Motorola	23/01/2020	12000

I have tried with Firstsortedvalue,by default it is showing Lastupdatedprice Product wise But i want to show LastupdatedPrice for Selecteddate.

Product	=FirstSortedValue(Price,-Date)

Samsung	19000
Apple	43000
Nokia	7500
Motorola	12000

I have generated missing dates with master calender and tried to populate missing Product & Price using Peek function But its nor working..

Product	Date	Price
Samsung	01/01/2020	15000
Apple	02/01/2020	45000
Nokia	03/01/2020	8000
Motorola	04/01/2020	11000
	05/01/2020
	06/01/2020
	07/01/2020
	08/01/2020
	09/01/2020
	10/01/2020
Samsung	11/01/2020	17000
Apple	12/01/2020	47000
Nokia	13/01/2020	9000
Motorola	14/01/2020	11500
	15/01/2020
	16/01/2020
	17/01/2020
	18/01/2020
	19/01/2020
Samsung	20/01/2020	19000
Apple	21/01/2020	43000
Nokia	22/01/2020	7500
Motorola	23/01/2020	12000

Re: Last updated Price for all Products based on date selection

sagarjagga — Mon, 17 Feb 2020 13:29:51 GMT

Hi Prathap,

You can create a calender date table which will not be associated with your data model i.e

Date:

Load Date_1 From ..... where Date_1 >= '$(MinDate)' and Date_1 <= '$(MaxDate)'

and use Date_1 in your filter. In measure column , write the set analysis like

=FirstStoredValue({<Date ={ "<= $(=Max(Date_1))">}Price,-Date)

Hope this will help!!!

Re: Last updated Price for all Products based on date selection

sagarjagga — Mon, 17 Feb 2020 13:31:44 GMT

Missed one bracket,

Set analysis will be like

=FirstStoredValue({<Date ={ "<= $(=Max(Date_1))"}>}Price,-Date)

Re: Last updated Price for all Products based on date selection

NitinK7 — Tue, 18 Feb 2020 10:48:34 GMT

Hi PrathapG,

check below code, that will be help you.

LOAD
product,
Date(date,'DD/MM/YYYY') as date,
price

FROM
sample.xlsx
(ooxml, embedded labels, table is Sheet3);

ABC_Map:
LOAD
product,
price as newprice,
Date( Max(date),'DD/MM/YYYY') as latest_date

Resident ABC
Group By product,price;

inner Join(ABC_Map)
LOAD
product,
max(latest_date)as latest_date
Resident ABC_Map
Group by product;

left Join (ABC)
LOAD
product,
newprice,
Date(latest_date,'DD/MM/YYYY') as latest_date
Resident ABC_Map;

DROP Table ABC_Map;

Regards,

Nitin.

Re: Last updated Price for all Products based on date selection

prathapG — Thu, 20 Feb 2020 11:23:13 GMT

Hi Sagarjagga,

Thanks for your revert

I tried the logic suggested, but not getting output, Please suggest anything missed in syntax.

I tried to show Product & Latest price in straight table.

Once again thanks for your support..

Re: Last updated Price for all Products based on date selection

sagarjagga — Thu, 20 Feb 2020 11:36:54 GMT

Hi Pratap,

Could you please share your test qvf.

For me it is working fine.We need to add second column as measure.

Re: Last updated Price for all Products based on date selection

prathapG — Thu, 20 Feb 2020 11:59:42 GMT

Hi Nitin,

Thanks for support

I tried logic suggested by you, It is working as suggested. But expected output is different.

Filter		Expected Output
Date			Date	Product	Last updated	Price
01/01/2020	01/11/2020	21/01/2020	17/01/2020	Samsung	11/01/2020	17000
01/02/2020	01/12/2020	22/01/2020	17/01/2020	Apple	12/01/2020	47000
01/03/2020	13/01/2020	23/01/2020	17/01/2020	Nokia	13/01/2020	9000
01/04/2020	14/01/2020		17/01/2020	Motorola	14/01/2020	11500
01/05/2020	15/01/2020
01/06/2020	16/01/2020
01/07/2020	17/01/2020
01/08/2020	18/01/2020
01/09/2020	19/01/2020
01/10/2020	20/01/2020

I want to show latest price for all products as on the selected date, The last updated date should be equal or less than the selected date.

Re: Last updated Price for all Products based on date selection

prathapG — Thu, 20 Feb 2020 12:31:58 GMT

Hi Sagarjagga,

Thanks

Please see enclosed attached qlikview app.

Re: Last updated Price for all Products based on date selection

sagarjagga — Thu, 20 Feb 2020 17:04:02 GMT

Hi Prathap,

It's working for me.I have done this in qliksense , ideally it should work in qlikview as well.

I m attaching qvf file

Re: Last updated Price for all Products based on date selection

prathapG — Mon, 24 Feb 2020 09:38:12 GMT

Hi Sagarjagga,

Thanks a lot

It is working as expected in Qlikview too...