Dynamic Sorting of Multiple Fields

[DataPirate26]

Hi all, 

I need to sort the Given Input Data for the "Nos of exceptions" all the fields. So basically the April Months Data shall come up first with no null rows in it. Then The May and June month Data will get appended and so on for all months ( As Provided in the Desired Output Data). As of now the data for all months comes up in a random order but I need to sort the order

Also If the Same Names or EMP ID comes for Multiple months only then then only it will be in the same row for all those months ( As shown in the Desired Output Data ( Check row 1, 11 and 12). 

[jdminton]

Ok, I'm not sure why the field name needs to stay the same. The length of the field name is unnecessary for detail. Since I needed to change it to sort dynamically, it was easier and more useful to not change it back. Now it is back to the original name. The sorting has been adjusted to meet the #2 requirement now. Please mark as solved. Thanks!

 

[DataPirate26]

@jdminton Thank you for all your assistance. But the Sort tool will not be able to handle it when data for July, August, Sept and so on will flow on to the workflow. 

The Sample data I have shared in only of 3 months. In future more data will flow in and that is where we need to take care of the sorting dynamically. You have set the order - 

1- Descending 

2- Descending 

3- Descending 

But when a new field will be populated with another months data then the sort tool will throw an error as it is not added in the sort tool and that is the only concern which I have and I am trying to say that only -Setting the order in the sort tool will not make it dynamic. 

[jdminton]

I'm working on this, but can you tell me how you would sort activity in April and June but not May? I don't think this sorting is possible even in manually Excel unless you are sorting by grand total at the top instead of column by column. 

Also, what are you trying to solve with the sorting? If it is putting the user with the most exceptions to the top, that can be handled with a total. Let me know, and I can adjust.

[DataPirate26]

@jdminton Sorting will me done one after the other For all months as the data comes. April, May, June, July, August and so on ..... The data will get appended in the same format. As of now we have data for 3 months So in the sort tool You have given 3 dates and the order as descending. Now when July months data will come Then again that needs to to appended in the same manner. But in the sort tool it won't be able to handle. 

That is the Desired Output Structure the user needs, I know that all data are correct and the values are flowing as expected but to achieve the particular structure we will have to work on the Dynamic Sorting and that i know is a challenge here 

[jdminton]

@DataPirate26 I played around with this for a bit today. There may be a way to combine some tools to do it with the tile tool, but I haven't quite gotten there yet. I did want to point out that sorting beyond one column doesn't really make sense if it isn't tied to the total. See in the image below where I added some additional exceptions to help demonstrate what happens to the sorting. Sorting the three columns in Excel scatters the results in the right two columns because they are dependent on the first column. I will still try to figure out a dynamic sort, but I would return to my customer and let them know other options. Why not just sort based on the latest column? Sorting the other ways just doesn't make business sense.

 

[DataPirate26]

@jdminton Thank you so much for the update. I completely agree with you that there is not much of a need on this. But we need to also Generate a Field as TOTAL at the End which will reflect the Summarized Total count of each Individual for all months together. 

For example : For Record 1: Nabatanu biswas the Total will be (2+2+0) = 4. 

Also it is easy to visualize the data Month Wise. That is why this is the Ask from the user end to keep the data sorted Column Wise. Also I see that the Row no 6: Nikunj Sau is showing in Red, but that won't be Red because Red is for only "Status = Inactive and Actual Count > 30". 

[jdminton]

The above image was modified in Excel to demonstrate the lack of structure created by sorting by columns - especially as more columns of data are added. The attached workflow has the total column as well. The sort itself is still manual until the dynamic piece is sorted out.

[DataPirate26]

@jdminton yes this looks fine. only Sorting the data is something which is the most challenging part here. Thank you for updating