Hi
Is it just me, or does anyone else start to write a post and in doing so manage to solve their own problem?
I have a data set that looks a bit like this:
Supplier | Awarded | Sum contract value | Count of contracts |
Supplier A | Yes | 12345 | 6 |
Supplier A | No | 10000 | 2 |
Supplier B | Yes | 112233 | 11 |
Supplier B | No | 12123 | 1 |
Supplier C | Yes | 333444 | 9 |
Supplier C | No | 34343 | 3 |
Supplier D | Yes | 666555 | 4 |
Supplier E | No | 10245 | 1 |
Supplier F | Yes | 555555 | 7 |
I want to sort it so that the output looks like this:
Supplier | Awarded | Sum contract value | Count of contracts |
Supplier B | Yes | 112233 | 11 |
Supplier B | No | 12123 | 1 |
Supplier C | Yes | 333444 | 9 |
Supplier C | No | 34343 | 3 |
Supplier F | Yes | 555555 | 7 |
Supplier A | Yes | 12345 | 6 |
Supplier A | No | 10000 | 2 |
Supplier D | Yes | 666555 | 4 |
Supplier E | No | 10245 | 1 |
The sort is based on the count of contracts field where 'Awarded'='yes', but I want to keep both (if present) entries for each supplier together, 'yes' in the 'Awarded' field should always come before 'no' if both are present.
The section of workflow I created to do this looks like this:
(I've attached the workflow in case anyone wants to look at it)
Now I'm wondering if there was a better way or what alternative ways there are to do this. Any ideas?
I didn't just delete the post because hopefully this will be useful to someone else.
Looking forward to seeing your alternative workflows.
PuffinPanic
Had a little play around and came up with this. Great that you figured it out but still posted for awareness & possible alternatives. Cheers!
My approach sorts on contract count to get the starting order of the Suppliers, then assigns a record ID once they're in the correct order, before filling the ID down to other records of the same supplier so they stick together as a pair in the final outcome. For the final result, I then sort on that Record ID before sorting descending on Awarded so that 'Yes' is always first.
@PuffinPanic , Here is the updated workflow you just need to use some of the preparation tools and you will get your desired result.
Thanks!!
Thanks everyone, some great alternative solutions there. I'll definitely be having a good look at them so that I can improve my own solution.
PuffinPanic