Alteryx Designer

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

Dynamic List Formula

5 - Atom

Hello Alteryx Community,

 

I have a specific problem that I couldn't find in the other topics. I have a simple data set that I am applying simple formulas to (in this example summing). However, There are several inputs that determine what would need to be summed. In the below example, the result would sum each of the entities in teh given list (eg List1 would be entiy 1 (50) + entity 2 (25) + entity 3 (75) + entity 4 (25) = 175. See my data set and various inputs below:

 

DATA SET:

EntityStateAmount
1AL50
2AR25
3IL75
4IL25
5AL125

 

STATE INPUT

StateGrouping
ALList1
ARList3
ILList2

 

 

LIST 1

Entity
1
2
3
4

 

LIST2

Entity
1
2
3

 

LIST3

Entity
1
3
5

 

 

DESIRED RESULT:

ListAmount
List1175
List2150
List3250

 

To take this one step further, I am looking for a way for the process to be dynamic enough so that if a new "List" was added, it would automatically be incorporated without having to copy/paste the steps to account for it. I have a workbook where each of the lists is a different tab so I was thinking I could get the list of tabs but don't know where to go after that. I have been struggling with this for a while. Any ideas? Thanks in advance for any assistance you're able to provide!

Highlighted
Alteryx
Alteryx

@bfolliard The easiest way to do this might be a with a multi-row formula tool. You could use a Find Replace to append the values to your data set for each "list #" Then the multi-row formula (tool mastery article) can be configured to "group by" the "List#" which should allow you to sum the values by making a formula like

 

If ISEMPTY(CurrentRow -1) - checking if we are in the top row of a list  then "CurrentRow" else "Current Row"+"[New Field] -1" endif

 

Then the last value for each list will be the total for that list. You can then sort, sample etc to isolate that value and ultimately Join back to list names with a select tool or display however you see fit. 

 

This whole process could also be done with a batch macro where: 

 

List Names are the "For Each" grouping variable and then the process inside would be similar to what is described above - the multi-row formula and some sort of Sort/Filter/Select tool to isolate the final totals and display the data you want.

 

Alternatively - if you can somehow get the List#'s to appear next to each item, and combine all of the lists so you have a column of "List#', Column with Entity, and a column with value, you could just use a summarize tool to "Group By List, Sum values

 

Sorry if that was a bit confusing! Hopefully this helps

 

Best,

Peter

Highlighted
11 - Bolide

I wonder if you could perhaps union the list/entities into one dataset? Something like this:

ListEntity
List 11
List 12
List 13
List 14
List 21
List 22
List 23
List 31
List 33
List 35

 

Then, you could simply join that to the State/Amount dataset on the entity ID.

To automate creation of the unioned list/entity dataset, you could use a Dynamic Input tool (see this article for instructions). Note, the format/schema for each tab in your workbook needs to be exactly the same for the Dynamic Input tool to work. If that's not the case, then you'll need to follow the second half of the linked article to create a macro.

Highlighted
5 - Atom

Hi Kelly,

 

Thanks for the response! I took your suggestion. I started by getting all the tab names from the workbook I am working in and then added a macro that takes reads in the data from each of the tabs. From there it functions very similar to your idea about unionizing the lists. Appreciate the help!

Labels