Add New Price Column

[v_jaideep]

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

[v_jaideep]

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

[QFabian]

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];

[v_jaideep]

@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.