hechesspee
New Member
- Joined
- Aug 16, 2020
- Messages
- 10
- Office Version
- 365
- Platform
- Windows
Hi,
I have the below data as input. I.e. multiple occurrences of Batchid, key, duration and units with varying line_number.
I want to get the below as output. i.e. if there is one occurence of Batchid, key, duration and units, retain as-is. If there are multiple occurrences, then aggregate it in units column.
Please advise a solution if there is one. I do not mind which line number gets retained in the output.
Thanks,
hechesspee
I have the below data as input. I.e. multiple occurrences of Batchid, key, duration and units with varying line_number.
| BatchId | line_number | key | duration | units |
| BatchId1234 | 173 | XY49682932 | 33 | 1 |
| BatchId1234 | 174 | XY49682933 | 60 | 1 |
| BatchId1234 | 175 | XY49682933 | 60 | 1 |
| BatchId1234 | 78 | XY53931814 | 14 | 1 |
| BatchId1234 | 165 | XY53931814 | 14 | 1 |
| BatchId5678 | 639 | XY37078370 | 2 | 1 |
| BatchId5678 | 640 | XY37078370 | 2 | 1 |
| BatchId5678 | 717 | XY52469026 | 18 | 1 |
| BatchId5678 | 718 | XY52469026 | 18 | 1 |
| BatchId9012 | 8 | XY54273628 | 15 | 1 |
| BatchId9012 | 9 | XY54273628 | 15 | 1 |
| BatchId9012 | 10 | XY54273628 | 15 | 1 |
| BatchId9012 | 11 | XY54273628 | 15 | 1 |
| BatchId9012 | 12 | XY54273628 | 15 | 1 |
I want to get the below as output. i.e. if there is one occurence of Batchid, key, duration and units, retain as-is. If there are multiple occurrences, then aggregate it in units column.
| BatchId | line_number | key | duration | units |
| BatchId1234 | 173 | XY49682932 | 33 | 1 |
| BatchId1234 | 174 | XY49682933 | 60 | 2 |
| BatchId1234 | 78 | XY53931814 | 14 | 2 |
| BatchId5678 | 639 | XY37078370 | 2 | 2 |
| BatchId5678 | 717 | XY52469026 | 18 | 2 |
| BatchId9012 | 8 | XY54273628 | 15 | 5 |
Please advise a solution if there is one. I do not mind which line number gets retained in the output.
Thanks,
hechesspee
