This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Now that I have the totals summed as I want. I want to assign a sub-category base don a hierarchy that is also dependent on the oldest/first Check Date for a respective ID #. I would like to create columns that assign the sub-category based on the priority from highest to lowest (total of 12 sub-categories listed from 1 to 12, where 1 is the highest priority and 12 being the lowest priority).
An ID may have 2 check dates of the same datestamp but can have different categories (See ID 6354), or have multiple sub-categories linked a different datestamp for each record for the same ID. But, I need the initial datestamp linked category to be assigned according to the priority list.
This is what I imagine the data to end up looking like - probably makes more sense than my explanation of it.
This is the prioritized hierarchy list for the sub-category:
I've tried it a few ways, with some transpose and summarize function. but not working. Also open to if there is a better way to set up the data than I have proposed, I am not a data scientist or expert.
Hi @Maneet_Deol, reading your note, looks like you are seeking help to populate columne 1st SUB-CAT, 2nd SUB-CAT etc., yes?
Can you walk us through the scenario in ID 5862? What would be your excel pseudo logic to populate its values in 1st SUB-CAT column? I am trying to understand the logic here before I can help code it in Alteryx.
The scenario for ID 5862 would look like as its shown in the screenshot. Since DOC is higher in priority in the sub-category list than CODE, DOC should populate the cell for that ID and Check Date combination in the 1st SUB-CAT column. The Subcategory for the same Check Date and ID, but with CODE will be CODE for that row. But, in the 2nd SUB-CAT column, it should read CODE for both records if ID.
I realize that may be very difficult to do, so if for simplicity sake both ID records show DOC in the 1st SUB-CAT, that should be fine too. Ex:
Hi @Maneet_Deol, I was able to codify your ask to replicate 1ST SUB-CAT in from your original post. I am not 100% confident about my approach being flexible to adapt to other data scenarios you may have. Certainly not my best work, but gets the job done for now. May someone else in the community has a better way of coding this down.
Play with it and let me know if there are any other data scenarios you have where it breaks. The trickiest part of this ask was thinking about the different scenarios and coming up with the logic in itself. I chose to use a combination of Priority, Count of ID's and Count of ID+Date combination. The heart of this workflow is in the last multi-row formula tool.
@AbhilashR Thank you for your efforts. For the most part I understand what you did. I am able to get the column to give me what I need, but there is a slight issue with some rows showing me "NEED TO CODE". I'm guessing that the multi-row formula needs more rows and sets up the logic accordingly?
Hi @Maneet_Deol, That is correct! I put together some logic in the multi-row formula tool to satisfy your requirement, but am fully aware that there are other scenarios I may not be aware of. Hence the 'NEED TO CODE' text for those rows and their scenario.
If you have other scenarios and are comfortable sharing some dummy data on them, send them across for us to look at.