Hi, I have a table like below,
| Date | order number | Chocolate number | length of the bar | Expected length | product code |
| 10/2/2023 | 1 | 1 | 8 | 8 | 101 |
| 11/2/2023 | 1 | 2 | 8 | 8 | 101 |
| 12/2/2023 | 1 | 3 | 8 | 8 | 101 |
| 13/2/2023 | 1 | 4 | 2 | 8 | 101 |
| 14/2/2023 | 1 | 4 | 8 | 8 | 101 |
| 15/2/2023 | 1 | 5 | 8 | 8 | 101 |
| 16/2/2023 | 1 | 6 | 8 | 8 | 101 |
| 17/2/2023 | 1 | 97 | 16 | 8 | 101 |
| 18/2/2023 | 2 | 98 | 16 | 16 | 102 |
| 19/2/2023 | 2 | 99 | 16 | 16 | 102 |
| 20/2/2023 | 2 | 100 | 16 | 16 | 102 |
Here, each order produces multiple chocolates. There are duplicate chocolate numbers because it was not produced as expected and considered scrap. So they produced the same chocolate bar again the next day. The manufacturing process is flexible, so 96 chocolate bars are produced in January for order number 2 and starting from 97 they are being produced in february.
If you closely check the chocolate number 97, the order number, expected length and product code still stayed the same as the previous row.
Now I would like to update the values of 3 columns - order number, product code and expected length for the chocolate number - 97.
I would like to do:
compare chocolate number for this row and previous row - if(( the number is same) or (next consequtive number)) then Do Nothing
else compare chocolate number for this row and next row - if(( the number is same) or (next consequtive number)) then replace the values of product code, order number and expected length for that row
Note: The assumption here is the table is ordered by Date.
I hope the question makes sense. Looking forward for some help.
Thank you in advance 🙂
