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

Assess a grouping of data for criteria

shertokj
8 - Asteroid

Hello Alteryx Community,

 

I am hoping to create a formula to assess a grouping of data for multiple criteria. I have used the tile tool to make the groupings, but now, need a way to write, "For each grouping, assess if a row in grouping has an "S" and is an "Asset" while having another row in the grouping that is a "H" and is an expense. If criteria is met for the grouping = True, if not = False."

 

I attached both sample data and expected output.

 

Thank you all in advanced for your help and time.

 

- Jonathan

8 REPLIES 8
MSalvage
11 - Bolide

@shertokj,

 

I would create a flag for the 2 conditions. I used 1 for 'Asset' and 'S', -1 for 'Expense' and 'H', and 0 for everything else. Then you can use a summarize tool to group by tile_num taking the max flag and min flag. If the max is 1 and the min is -1 then you have a success. Simply Joining this back to the data gives the desired result. Example workflow attached.

 

Best,

MSalvage

Nurul
7 - Meteor

Hi @shertokj

 

Here I attached my solution,I use multi-row formula tool to create condition.Hope this will help you.

shertokj
8 - Asteroid

Thank you for your very prompt reply. Unfortunately, I am running Alteryx 10.5 and cannot open your example. Can you use an older file format or take screenshots? Thank you!

MSalvage
11 - Bolide

Below is the workflow itself:

 

shertokj Workflow Image.PNG

 

Here is the first formula tool:

shertokj first formula.PNG

and Second formula:

shertokj second formula.PNG

 

Hope this makes sense,

MSalvage

 

Nurul
7 - Meteor

Hi @shertokj

 

Here I attached the screenshot of my workflow.

 

Capture.JPGCapturess.JPGf.JPG

 

Hope you can understand my workflow.

shertokj
8 - Asteroid

Hi @Nurul and thank you for posting a screenshot of your flow. I believe we are missing one element in the multi-row formula because if a Tile has multiple sequences and there are both True and False situations within, it will always return a false negative. Is there anyway we can build in the multi-row formula to search all sequences in a tile for both scenarios "True" and if so, make the whole tile 'True'?

 

Thank you

 

 

MSalvage
11 - Bolide

@shertokj,

 

Did you ever look back at my solution? It should solve the problem.

 

-MSalvage

shertokj
8 - Asteroid

@MSalvage, yes, I looked at your solution and it worked. I just liked how the other utilized less tools. But yours definitely worked.

 

I appreciate your solution!

Labels