Identifying combination numeric cells which contribute to a total

Hi all!

Newish Alteryx user here, quickly building my toolkit but much yet to learn. Very recently started lurking on the Community board and love this place. Friendly, knowledgeable, helpful people. I look forward to one day being able to add my value back to the community.

I have what is for me a thorny problem and seek your wisdom.

Visualize an Excel-like table of, say, 40 fields. All numeric. Many fields are 0 (none are null); but one or more contain values.

A final, 41st field is a magic total; and our goal is to identify the combination of cells in that row whose sum equal that total. The row may or may not contain other numerical values that don't contribute to that total.

Example: final field = $310.25. 40 fields of data contains [interspersed among many zeros] $100.00, $404.18, $104.00, $110.25, $475.66, $63.99, $89.02, $36.01, $110.00.

The human eyeballing it can fairly quickly pick out 36.01, 63.99, 100, 110.25. In this example, 404.18, 104, 475.66, and 89.02 are not related to the solution.

I'm looking for a way for Alteryx to tell me this is the correct group of items. Results in the form of individual amounts would be usable; results in the form of precise fieldnames (i.e. "for this record you want this field, this field, this one etc") would be ideal.

I have tried some different approaches but am not particularly close to solution. I sense that it MIGHT be doable with some kind of macro, but as I said, I am quite new and am not yet a Jedi in the use of macro's. And maybe it's doable without macro's.

Any guidance is appreciated!!

Common Use Cases

Accepted answers

jdunkerley79

It is do-able but this is a computationally difficult problem for a computer.

I think the optimiser tool should be able to help but haven't used it enough yet, so here is a brute force solution.

It works as follows:

Add a Record ID
Transpose from columns to rows
Filter out all invalid values (either 0 or greater than the total)
Count the values remaining for each RecordID and generate a set of scenarios (equal to 2^Count of Values)
Compute the totals for each scenario and keep the first one which matches the total
Pick back out the values which made this scenario and display in results

A bit fiddly (but this is a hard problem!).

Combinations.yxzp

All comments

jdunkerley79

It is do-able but this is a computationally difficult problem for a computer.

I think the optimiser tool should be able to help but haven't used it enough yet, so here is a brute force solution.

It works as follows:

Add a Record ID
Transpose from columns to rows
Filter out all invalid values (either 0 or greater than the total)
Count the values remaining for each RecordID and generate a set of scenarios (equal to 2^Count of Values)
Compute the totals for each scenario and keep the first one which matches the total
Pick back out the values which made this scenario and display in results

A bit fiddly (but this is a hard problem!).

Combinations.yxzp

Storm

A belated thank you very much for your reply and your help! I DL'd your flow and am working through it. I understand probably 80% of it, but that other 20% has me somewhat mystified I need to understand it better, so I am working my way through it.

It looks like pretty likely this will work for my purposes, and I appreciate the help! Thank you again!

jdunkerley79

Should work but possible I missed a case. Not in front of Alteryx at moment will check in a couple of hours.

Storm

I was able to figure this one out Smiley Happy

Filter tool had this:

[Value] != 0 and [Value] < [Total]

But this excludes values that don't match to the total. Thus, changing to:

[Value] != 0 and [Value] <= [Total]

Did the trick!

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335