Hello,
I have a question on selecting top N rows for a data set. I understand I can use TOP N rows from Sample tile to select number of rows I want but what I am looking for is something dynamic.
Here is the sample data set. Data has been grouped by the field Number and ranked accordingly. However to each group of unique "Number" I need to assign a new field half rows as 0 and half rows as 1. If the number of rows is N and for a group of N is even I need First N/2 rows as 1 and remaining as 0, however if N is odd then first N/2+1 rows is 1 and remaining 0.
| Name | Number | Rank |
| John | 1234567890 | 1 |
| Jane | 1234567890 | 2 |
| John | 1234567890 | 3 |
| Jane | 1234567890 | 4 |
| John | 1234567890 | 5 |
| Jane | 1234567890 | 6 |
| Shawn | 2345678901 | 1 |
| Shane | 2345678901 | 2 |
| Shawn | 2345678901 | 3 |
| Shane | 2345678901 | 4 |
| Shawn | 2345678901 | 5 |
| Shane | 2345678901 | 6 |
| Shawn | 2345678901 | 7 |
| Shane | 2345678901 | 8 |
| Shawn | 2345678901 | 9 |
| Jorge | 3456789101 | 1 |
| Lu | 3456789101 | 2 |
From the sample data set above I will need:
| Name | Number | Rank | Decision |
| John | 1234567890 | 1 | 1 |
| Jane | 1234567890 | 2 | 1 |
| John | 1234567890 | 3 | 1 |
| Jane | 1234567890 | 4 | 0 |
| John | 1234567890 | 5 | 0 |
| Jane | 1234567890 | 6 | 0 |
| Shawn | 2345678901 | 1 | 1 |
| Shane | 2345678901 | 2 | 1 |
| Shawn | 2345678901 | 3 | 1 |
| Shane | 2345678901 | 4 | 1 |
| Shawn | 2345678901 | 5 | 1 |
| Shane | 2345678901 | 6 | 0 |
| Shawn | 2345678901 | 7 | 0 |
| Shane | 2345678901 | 8 | 0 |
| Shawn | 2345678901 | 9 | 0 |
| Jorge | 3456789101 | 1 | 1 |
| Lu | 3456789101 | 2 | 0 |
Is this possible?
Thanks in advance.
