Alteryx Designer Desktop Discussions

rockeylearning · ‎09-01-2023

Hi Alteryx Community!

Is there a way to sort with logics?

Sample data

sample (2).png

Output data

for example, column A, those numbers that are 10 digits like 2 thru 4 will stay on the top. Then if column B if 1000 = 1000 from column A, then move the whole row below row 5 and repeating for the next list.

I'm sorry, I don't even know what this type of sorting would call.

The sample Data is based off what I've already constructed in the workflow as seen below, but then I get stuck toward the end at the sorting

Appreciated any input.

ScottLewis · ‎09-01-2023

I don't see a way to do this without creating an extra field first to do the sort. You're basically clustering on the depth 1 members, so you would need a field to establish that clustering and then probably a second tier of sort on Parent to keep the depth 1 member above the depth 2 members.

Separately from the pure how to Alteryx question, it looks like you're working with hierarchical (OLAP or somesuch) data. Sorting, and generally working with in Alteryx, that sort of data is easier with a dimension table type representation, rather than parent/child pairs. Depending on how your source data looks this may be easier with some design changes upstream of the sort.

jrgo · ‎09-01-2023

I usually create new pseudo columns (i.e. Member-SORT, Parent-SORT, etc.) that would apply replace a value with another value that would result in the desired sorting behavior. Since you're not altering the actual values, after it's sorted how you want, you can then remove them.

For example, the expression I'd create Member-SORT would be

IF LENGTH([Member]) = 10 THEN 1 ELSE 2 ENDIF

Then on the sort tool, you'd sort on that field first in ascending order then set the [Member] field as the second sort field.

Unfortunately, I don't understand the logic you're trying to apply against [Parent] to give an example for that. However, I'm hoping this suggestion sparks some new ideas for you explore.

Amol_Telore · ‎09-03-2023

Hi @rockeylearning

With the help of Iteration macro, I was able to generate the output you are looking for. Attached is the workflow and output snapshot. Hope it solves your problem.

rockeylearning · ‎09-04-2023

@Amol_Telore Thank you! This is exactly what I was looking for.

Can you tell me more about the macro? In my Designer, I don't have the blank blue icon like your, I only have the two gray one like below. I would like to learn more about this so I can better understand how this work.

rockeylearning · ‎09-04-2023

@ScottLewis thank you for pointing out some details, it is correct that this is a hierarchy. I did some more filter and using Transpose to get the data I need, then with @Amol_Telore solution, I was able to get the hierarchy I needed.

rockeylearning · ‎09-04-2023

@jrgo thank you for spark some ideas, I ended up doing some additional filtering with Transpose and get the data I need, then I used @Amol_Telore solution that provided the final result I needed.