Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
@Gysbert_Wassenaar , May be you can guide/help me? 🙂 I have tried using firstsortedvalue() . Any suggestion that can be implemented in scripting .
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];
@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.