Scenario
I have the below data where corresponding to each Company, SKU, Year, Week, Product Type we have Qty.. We are trying to get prior year corresponding week Qty in a separate column.. Same SKU can be available across multiple types so we need to take the unique combination and look up what' s the corresponding value in prior year.
Example
e.g. corresponding to 1st row the value expected will be 610 (that is the value for officemax,SK1,2019,week 1).. similarly corresponding to Lowes,SK1,2020,Week3 will be 680.. and so on. If the corresponding value does not exist in a prior year it can be 0 or null..
Dataset
| Company | SKU | Week | Year | Qty | Product Type |
| Officemax | SK1 | 1 | 2020 | 500 | Home Supplies |
| Officemax | SK2 | 1 | 2020 | 400 | Home Supplies |
| Officemax | SK1 | 2 | 2020 | 350 | Home Supplies |
| Officemax | SK1 | 1 | 2020 | 980 | Electronics |
| Officemax | SK2 | 1 | 2020 | 170 | Electronics |
| Officemax | SK1 | 1 | 2019 | 610 | Home Supplies |
| Officemax | SK2 | 1 | 2019 | 120 | Home Supplies |
| Officemax | SK2 | 2 | 2019 | 350 | Home Supplies |
| Officemax | SK1 | 1 | 2019 | 900 | Electronics |
| Officemax | SK2 | 1 | 2019 | 1400 | Electronics |
| Officemax | SK2 | 1 | 2018 | 1200 | Home Supplies |
| Officemax | SK3 | 1 | 2018 | 556 | Home Supplies |
| Officemax | SK1 | 1 | 2018 | 340 | Electronics |
| Lowes | SK1 | 1 | 2020 | 7500 | Home Supplies |
| Lowes | SK1 | 3 | 2020 | 6500 | Home Supplies |
| Lowes | SK1 | 1 | 2019 | 680 | Home Supplies |
| Lowes | SK1 | 3 | 2019 | 520 | Home Supplies |
