Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
Can some one help with grouping of data. For the below source data, I need output for two different set of dimensions.
- Trying this for Page break
- Grouping to be done for each ID. This will be the cycling column.
Source:
ID | Employee | City | Transaction | Code |
1 | Raju | Chennai | AA | 1 |
1 | Raju | Chennai | AA | 1 |
1 | Raju | Chennai | AA | 1 |
1 | Raju | Chennai | BB | 2 |
1 | Raju | Chennai | BB | 2 |
1 | Raju | Hyderabad | BB | 3 |
1 | Raju | Hyderabad | BB | 3 |
1 | Raju | Hyderabad | BB | 4 |
1 | Raju | Hyderabad | BB | 4 |
1 | Raju | Pune | BB | 5 |
1 | Raju | Pune | BB | 5 |
1 | Rahul | Chennai | AA | 6 |
1 | Rahul | Chennai | AA | 6 |
1 | Rahul | Chennai | AA | 6 |
1 | Rahul | Hyderabad | CC | 6 |
1 | Rahul | Hyderabad | DD | 6 |
1 | Ravi | Hyderabad | EE | 6 |
1 | Ravi | Delhi | EE | 6 |
1 | Ravi | Delhi | EE | 6 |
1 | Ravi | Delhi | EE | 6 |
1 | Ravi | Delhi | EE | 6 |
2 | Rahul | Hyderabad | DD | 6 |
2 | Ravi | Hyderabad | EE | 6 |
2 | Ravi | Delhi | EE | 6 |
Output1:
Employee | Transaction | Code | Grouping |
Raju | AA | 1 | Set1 |
Raju | AA | 1 | Set1 |
Raju | AA | 1 | Set1 |
Raju | BB | 2 | Set1 |
Raju | BB | 2 | Set1 |
Raju | BB | 3 | Set1 |
Raju | BB | 3 | Set1 |
Raju | BB | 4 | Set1 |
Raju | BB | 4 | Set1 |
Raju | BB | 5 | Set1 |
Raju | BB | 5 | Set2 |
Rahul | AA | 6 | Set2 |
Rahul | AA | 6 | Set2 |
Rahul | AA | 6 | Set2 |
Rahul | CC | 6 | Set2 |
Rahul | DD | 6 | Set2 |
Ravi | EE | 6 | Set2 |
Ravi | EE | 6 | Set2 |
Ravi | EE | 6 | Set2 |
Ravi | EE | 6 | Set3 |
Ravi | EE | 6 | Set3 |
Rahul | DD | 6 | Set1 |
Ravi | EE | 6 | Set2 |
Ravi | EE | 6 | Set3 |
Output 2:
Employee | City | Code | Grouping |
Raju | Chennai | 1 | Set1 |
Raju | Chennai | 1 | Set1 |
Raju | Chennai | 1 | Set1 |
Raju | Chennai | 2 | Set1 |
Raju | Chennai | 2 | Set1 |
Raju | Hyderabad | 3 | Set1 |
Raju | Hyderabad | 3 | Set1 |
Raju | Hyderabad | 4 | Set1 |
Raju | Hyderabad | 4 | Set1 |
Raju | Pune | 5 | Set1 |
Raju | Pune | 5 | Set2 |
Rahul | Chennai | 6 | Set2 |
Rahul | Chennai | 6 | Set2 |
Rahul | Chennai | 6 | Set2 |
Rahul | Hyderabad | 6 | Set2 |
Rahul | Hyderabad | 6 | Set2 |
Ravi | Hyderabad | 6 | Set2 |
Ravi | Delhi | 6 | Set2 |
Ravi | Delhi | 6 | Set2 |
Ravi | Delhi | 6 | Set3 |
Ravi | Delhi | 6 | Set3 |
Rahul | Hyderabad | 6 | Set1 |
Ravi | Hyderabad | 6 | Set2 |
Ravi | Delhi | 6 | Set3 |
For your grouping you could try: 'Set' & ceil(rowno() / 10) as Grouping.
- Marcus
Thanks Marcus. I am actually looking for a piece of code on how can I assign rowno() or recno() for the above input date to getting the provided outputs.
Any suggestions please.
Not sure I understand what you are trying to do here. Would you be able to elaborate on your expected outputs?
Outpu1:
For each ID, I need grouping of 10 for target columns Employee, Transaction, Code
Output2:
For each ID, I need grouping of 10 for target columns Employee, City, Code
Note: Target columns are not the Key combinations for grouping. These are just output columns. A single group can have multiple combinations of target columns.
Is this something you need in the script or front end? I guess the front end, because this seems like somewhat dynamic?
Need in the script.
very short example
Input:
ID | City | Employee | Transaction | Code |
---|---|---|---|---|
1 | Chennai | Raju | AA | 1 |
1 | Chennai | Rahul | AA | 6 |
1 | Delhi | Ravi | EE | 6 |
1 | Hyderabad | Raju | BB | 2 |
1 | Hyderabad | Raju | BB | 3 |
1 | Hyderabad | Raju | BB | 4 |
1 | Hyderabad | Rahul | CC | 6 |
1 | Hyderabad | Rahul | DD | 6 |
1 | Hyderabad | Ravi | EE | 6 |
1 | Lucknow | Charan | CCC | 116 |
1 | Lucknow | Charan | DDD | 1166 |
1 | Mumbai | Hari | ZZ | 222 |
1 | Mumbai | Hari | ZZA | 333 |
1 | Pune | Raju | BB | 5 |
2 | Delhi | Ravi | EE | 6 |
2 | Hyderabad | Rahul | DD | 6 |
2 | Hyderabad | Ravi | EE | 6 |
Output1: Gives us 15 Rows
ID | Employee | Transaction | Code | Group |
1 | Charan | CCC | 116 | Set1 |
1 | Charan | DDD | 1166 | Set1 |
1 | Hari | ZZ | 222 | Set1 |
1 | Hari | ZZA | 333 | Set1 |
1 | Rahul | AA | 6 | Set1 |
1 | Rahul | CC | 6 | Set1 |
1 | Rahul | DD | 6 | Set1 |
1 | Raju | AA | 1 | Set1 |
1 | Raju | BB | 2 | Set1 |
1 | Raju | BB | 3 | Set1 |
1 | Raju | BB | 4 | Set2 |
1 | Raju | BB | 5 | Set2 |
1 | Ravi | EE | 6 | Set2 |
2 | Rahul | DD | 6 | Set1 |
2 | Ravi | EE | 6 | Set1 |
Output2: Give us 16 Rows
ID | Employee | City | Code | Output |
1 | Charan | Lucknow | 116 | Set1 |
1 | Charan | Lucknow | 1166 | Set1 |
1 | Hari | Mumbai | 222 | Set1 |
1 | Hari | Mumbai | 333 | Set1 |
1 | Rahul | Chennai | 6 | Set1 |
1 | Rahul | Hyderabad | 6 | Set1 |
1 | Raju | Chennai | 1 | Set1 |
1 | Raju | Hyderabad | 2 | Set1 |
1 | Raju | Hyderabad | 3 | Set1 |
1 | Raju | Hyderabad | 4 | Set1 |
1 | Raju | Pune | 5 | Set2 |
1 | Ravi | Delhi | 6 | Set2 |
1 | Ravi | Hyderabad | 6 | Set2 |
2 | Rahul | Hyderabad | 6 | Set1 |
2 | Ravi | Delhi | 6 | Set1 |
2 | Ravi | Hyderabad | 6 | Set1 |
Any suggestions with the short example I provided?