Assigning Hierarchy to Data Set

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-Sum-with-Alteryx-workflow/m-p/546430/highlight/true#M117906

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

Alteryx Help Excel V2.xlsx

Alteryx Help Hierarchy File.xlsx

Workflow

Datasets

Tips and Tricks

Transformation

Optimization

Accepted answers

AbhilashR

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!

sampleGrouping.yxmd

All comments

AbhilashR

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

@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 Date	Sub-Category	Denied Amount	Initial Denied Amount	1st SUB-CAT	2nd SUB-CAT
5862	2/11/2020	DOC	$ 144.00	$ 288.00	DOC	CODE
5862	2/11/2020	CODE	$ 144.00	$ 288.00	DOC	CODE

I hope that helps. Thank you again.

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235