Dynamic Sorting of Multiple Fields

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).

Dynamic Sorting of Multiple Fields.yxmd

Dynamic Processing

Accepted answers

All comments

@DataPirate26 ! The desired output seems to contain different data from the input, but I think this is what you're looking for. Let me know.

Dynamic Sorting of Multiple Fields.yxmd

@jdminton The Output Looks Completely fine except for I missed out on 1 thing that is I need the "Actual Count" and "Flag" Field as well in the Output. When I am keeping those field in the Summarize tool the desired output changes , and for the same names the values doesn't comes in the same rows. Can you please blend the data and update the workflow ? Also I need to take care of these months dynamically. As of now I have data for only these 3 months but this Won't be the same the months need to be populated dynamically. Will the Sort tool at last be able to handle that as When data will come for more months those fields won't be there in the sort right

@binu_acs do you have a solution for this ? Any help will be appreciated.

@DataPirate26 as @jdminton mentioned the expected output is not matching with the input file you provided. Can you update your output file based on your input file?

@binu_acs Please see the below attached excel file for more clarity. The desired output shall look exactly as shown below. I don't want the Actual Count and Flag field in the Final output, but they need to be used in the reporting tool for providing the color legends as mentioned in the excel file. Let me know if you can help to get the desired output with the color legends

Required Output for Dynamic Sorting.xlsx

@binu_acs Let me know if you have any solution on this.

@DataPirate26 Your request is quite complex. I would suggest using this workflow and conditional formatting in Excel for the cell color. I tried coloring the cells in Alteryx, but to do this dynamically, I'm running into the issue that I cannot crosstab after. I'm not certain that all of it can be done in Alteryx.

This layout will provide the cell color in a dynamic column at the end. The column can be used to conditionally format the amount column. Let me know how this works for you.

Dynamic Sorting of Multiple Fields-new.yxmd

@jdminton Thanx for your response. This would need to again have a manual intervention which I don't want. I developed a solution earlier which has the data and the colors coming but i was not able to sort the data in the required order which needs to be done. Also once I removed the Flag and the Actual Count field from the table tool as the column names are coming up dynamically so this was also throwing error. Thank you for your help though this was also a great appraoch

I actually had another idea, and it works! We might need to play with it to adjust, but this provides the cell formatting for the different values by month. It is also dynamic and will add new fields as they come and apply the logic for the formatting. Once you've confirmed this works with your data, we can add the layout and render tools. After you connect to your data, please share a workflow with some data if you are having any issues.

Dynamic Sorting of Multiple Fields-updated.yxmd

The Data and the Coloring is fine but again if you see the sorting is not as of the desired output. The data for the months needs to be appended as shown below in the output. Only if an individual has exceptions for 2 months or 3 months that shall come up in the same row.

Note: The data for April shall come together then after that the data for May shall start and then June and so on. Your output is completely OK but that dynamic sorting of the data month wise is the main challenge. Also the field names for the Months should be as shown in the output and they shall come up dynamically.

Required Output for Dynamic Sorting.xlsx

@jdminton Can this dynamic sorting be done with this data ? I am able to achieve the same output as well but I am not able to sort the data month wise one after the other and that is the main challenge.

@DataPirate26 Can you explain what you mean? The workflow I provided sorts the columns by month currently. Maybe an image of your issue?

As a side note, the user community is here to help. I've given you a way to get the data out and formatted. A little effort on your part to try sorting or whatever else you are needing should occur before asking for it to be fixed.

@jdminton Please see the below attached screenshot for better understanding.

The Field names shall come as "Nos of Exceptions - April 2023", "Nos of Exceptions - May 2023" and so on
See how each months data are grouped one after the other. Need to sort it in the same way but when again in future the other months data will come it shall follow the same pattern so it need to be dynamic

It is set up to be dynamic for future months. The new column is driven by formulas.

@jdminton The data is completely fine the months are coming up dynamically I understand that. But the sorting is not according to the desired format. If you sort by the months then the data will be coming exactly how it is shown in the Output but the challenge is that the months will be changing in future so the sort tool will throw an error when new data will come up in future. Is there a way to handle that ?

@DataPirate26 I see what you are saying. For that type of sort, I needed to create a formula to create an order for all the included months.

Dynamic Sorting of Multiple Fields-updated.yxmd

@jdminton Thank you for all your help on this. I guess the required format won't be possible to achieve with this data. We tried a lot on this but I haven't been able to figure out a solution to this. In case you have one you can let me know.

@DataPirate26 Can you tell me what about the latest workflow is not meeting your requirements? We were able to dynamically add fields, format the cells dynamically, and sort dynamically for those with amounts in more than one month. I'm a little perplexed why you are saying that we haven't gotten a solution.

@jdminton Can you please check the below attached Output for Reference on how the data needs to be shown in the Output.

The Field names shall come as "Nos of Exceptions - April 2023", "Nos of Exceptions - May 2023" and so on
See how each months data are grouped one after the other. Need to sort it in the same way but when again in future the other months data will come it shall follow the same pattern so it need to be dynamic.

Sort dynamically .png

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!

Dynamic Sorting of Multiple Fields-updated.yxmd

@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.

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.

@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

@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.

@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".

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.

Dynamic Sorting of Multiple Fields-updated_Total.yxmd

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

Quick Links

Tags

This months top contributors

atcodedog05 19458

Qiu 15866

MarqueeCrew 13708

apathetichell 13703