topic Add New Price Column in QlikView

Add New Price Column

v_jaideep — Mon, 29 Mar 2021 14:37:48 GMT

Experts,

Below is the table structure I have .

Table A:
Start_date,
End_date,
Local_Price,

currency,
key

Table B:
Launch_date,
product,country,area,region,province,size,unit

name,
key

Product	price	launch_date	start date	end date
AB	3788	4/2/2019	10/28/2019
AB	4450	4/2/2019	4/2/2019	10/27/2019
AC	12.52	3/1/2019	1/1/2021
AC	13	3/1/2019	8/3/2019	12/31/2020
AC	18.52	3/1/2019	3/1/2019	8/2/2019
AD	100	11/1/2019	1/1/2021
AD	110	11/1/2019	6/4/2020	12/31/2020
AD	80	11/1/2019	3/2/2020	6/3/2020
AD	90	11/1/2019	11/1/2019	3/1/2020

Expected data in table should be as below: Launch_price and Current_Price we will get to know from launch_date, start date and end date.

Product Current_Price Launch_Price

AB 3788 4450

AC 12.52 18.52

AD 100 90

Re: Add New Price Column

v_jaideep — Mon, 29 Mar 2021 14:43:12 GMT

@Gysbert_Wassenaar , May be you can guide/help me? 🙂 I have tried using firstsortedvalue() . Any suggestion that can be implemented in scripting .

Re: Add New Price Column

QFabian — Mon, 29 Mar 2021 14:57:57 GMT

Hi @v_jaideep , please see if this works for you:

Current_Price:
Load
key,
Local_Price as Current_Price
Resident [Table A]
Where isnull([end date]) or [end date] = '';

Launch_Price:
Load
key
min(Start_date) as min_launch_date
Resident [Table A]
Where not isnull([end date]) and [end date] <> ''
group by
key;

left join
Load
key,
Start_dateas min_launch_date
Resident [Table A];

Re: Add New Price Column

v_jaideep — Mon, 29 Mar 2021 16:17:02 GMT

@QFabian , Thank you for your response. I did not get why min launch date was considered when we are looking for prices columns(Current and launch price). Also we have launch_date from Table B.