Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Assigning Hierarchy to Data Set

Maneet_Deol
7 - Meteor

Hello, 

 

This is a continuation of a previous post that @AbhilashR helped me solved. Link to previous post: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Summarizing-Calculation-Conditional-Su... 

 

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. 

2020-03-25 16_06_24-HealthRise - Desktop Viewer.png

 

 

 

 

 

 

 

 

 

 

This is the prioritized hierarchy list for the sub-category:

 

Hierarchy List.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

 

Thank you in advance! 

 

@AbhilashR 

7 REPLIES 7
AbhilashR
15 - Aurora
15 - Aurora

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.

Maneet_Deol
7 - Meteor

@AbhilashR To answer your first question: yes.

 

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:

ID #Check DateSub-Category Denied Amount Initial Denied Amount1st SUB-CAT2nd SUB-CAT
58622/11/2020DOC $                    144.00 $                        288.00DOCCODE
58622/11/2020CODE $                    144.00 $                             288.00DOCCODE

 

I hope that helps. Thank you again.

Maneet_Deol
7 - Meteor

@AbhilashR After some thought, I think for the sake of simplicity, the 1st SUB-CAT column is all I think I will need. Ignoring 2nd, 3rd, and beyond.

AbhilashR
15 - Aurora
15 - Aurora

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.

 

Thanks for posting this ask!

Maneet_Deol
7 - Meteor

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

AbhilashR
15 - Aurora
15 - Aurora

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. 

Maneet_Deol
7 - Meteor

Thanks @AbhilashR  You helped me out a lot!

Labels