How to check hierarchy if the account id has Parent ONLY, Child ONLY, or BOTH in the list.

[gizmoholic]

Hi community,

I am very new with Alteryx, i mainly use Excel and SQL.

In the attached excel file is the sample data set wherein i need to determine if a specific record is only available at Parent level (Parent ONLY), Child level (Child ONLY), or both levels exist in the list. In reality, i have millions of records to run, so using excel will be inefficient which is why i'm hoping this function can be replicated here.

The hierarchy is determined by:

If Account Id = Parent Id, then it is a Parent account.

[messi007]

@gizmoholic,

Please try this:

Attached the workflow,

If this solves your issue please mark the answer as correct, it helps others 🙂

Regards,

[atcodedog05]

Hi @gizmoholic 

Here is a workflow for the task. Getting output same as expected output.

Output:

Workflow:

Hope this helps 🙂 Feel to ask if you have any questions

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[gizmoholic]

Hi @messi007

Thank you for your response, I think you are on the right direction however when i tested this in a larger dataset it incorrectly counts those with "," as Both even if the result is either "Parent,Parent,Parent" (this should be counted as Parent Only) or "Child,Child,Child" (should be considered as "Child only)... It should only count result as Both if it has the combination of both Parent AND Child in a single concatenate row.

[atcodedog05]

Hi @gizmoholic 

Did you give my one a try. I tried to build a logic based on the explanation. And i am getting expected output.

Hope this helps 🙂

[messi007]

@gizmoholic,

Please try this 🙂

Below the result

Attached the workflow,

Hope that helps,

[gizmoholic]

hi @atcodedog05

I tried your solution, to clarify, should the result of the Left Join be "Child Only" instead of also being "Parent only" like the Right Join?

The problem i encountered aside from this is when I scaled it to the larger dataset, the inner join resulted in multiple duplicates (no issue with the outer joins). The account ids and parent ids are not unique records, there would be repeated instances in the actual data set. I'll try to add a new one in a bit. I appreciate your help

[atcodedog05 · Accepted solution]

@gizmoholic 

Replacing as "Child only" you are right i did a copy paste and forgot to change 😅

Edited it now.

And about duplicates yes it does create duplicates. Maybe you can use a unique tool on Id and Parent ID and filter it do the relationship match. And later add them back to original data using find and replace tool.

Hope this helps 🙂

[gizmoholic]

Hi @messi007

I've tried your revised solution, i am attaching a larger dataset because the items being missed are those with different combinations. Is there a formula that would count the instances of "Parent" and "Child" in Concat_Hierarchy?

something like the countif in excel in my original post, but i'm not versed yet with Alteryx formula syntax.

IF count of ("Parent" in Concat_hierarchy>0) AND ("Child" in Concat_hierarchy>1) then "Both"

elseif count of ("Parent" in Concat_hierarchy>0) AND ("Child" in Concat_hierarchy=0) then "Parent Only"

else count of ("Parent" in Concat_hierarchy=0) AND ("Child" in Concat_hierarchy>0) then "Child Only"

endif

[gizmoholic]

im currently testing the result with the one from Excel (very slow), will let you know if it is accurate.

cheers