Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
prathapG
Partner - Contributor
Partner - Contributor

Last updated Price for all Products based on date selection

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:

ProductDatePrice
Samsung01/01/202015000
Apple02/01/202045000
Nokia03/01/20208000
Motorola04/01/202011000
Samsung11/01/202017000
Apple12/01/202047000
Nokia13/01/20209000
Motorola14/01/202011500
Samsung20/01/202019000
Apple21/01/202043000
Nokia22/01/20207500
Motorola23/01/202012000

 

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)
  
Samsung19000
Apple43000
Nokia7500
Motorola12000

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

ProductDatePrice
Samsung01/01/202015000
Apple02/01/202045000
Nokia03/01/20208000
Motorola04/01/202011000
 05/01/2020 
 06/01/2020 
 07/01/2020 
 08/01/2020 
 09/01/2020 
 10/01/2020 
Samsung11/01/202017000
Apple12/01/202047000
Nokia13/01/20209000
Motorola14/01/202011500
 15/01/2020 
 16/01/2020 
 17/01/2020 
 18/01/2020 
 19/01/2020 
Samsung20/01/202019000
Apple21/01/202043000
Nokia22/01/20207500
Motorola23/01/202012000

 

1 Solution

Accepted Solutions
sagarjagga
Creator
Creator

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

View solution in original post

9 Replies
sagarjagga
Creator
Creator

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!!!

sagarjagga
Creator
Creator

Missed one bracket,

Set analysis will be like

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

NitinK7
Specialist
Specialist

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;

 

img.PNG

Regards,

Nitin.

prathapG
Partner - Contributor
Partner - Contributor
Author

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.

Price_Master.png

 
 

Once again thanks for your support..

 
 
 

 

sagarjagga
Creator
Creator

Hi Pratap,

Could you please share your test qvf.

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

prathapG
Partner - Contributor
Partner - Contributor
Author

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   DateProductLast updatedPrice
01/01/202001/11/202021/01/2020 17/01/2020Samsung11/01/202017000
01/02/202001/12/202022/01/2020 17/01/2020Apple12/01/202047000
01/03/202013/01/202023/01/2020 17/01/2020Nokia13/01/20209000
01/04/202014/01/2020  17/01/2020Motorola14/01/202011500
01/05/202015/01/2020      
01/06/202016/01/2020      
01/07/202017/01/2020      
01/08/202018/01/2020      
01/09/202019/01/2020      
01/10/202020/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.

 

 

 

prathapG
Partner - Contributor
Partner - Contributor
Author

Hi Sagarjagga,

Thanks

Please see enclosed attached qlikview app.

sagarjagga
Creator
Creator

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

prathapG
Partner - Contributor
Partner - Contributor
Author

Hi Sagarjagga,

Thanks a lot

It is working as expected in Qlikview too...