Identifying combination numeric cells which contribute to a total

[Storm]

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!!

[jdunkerley79 · Accepted solution]

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

A similar question was posed a little while ago: http://community.alteryx.com/t5/Data-Preparation-Blending/Finding-the-combination-that-adds-up-to-a-...

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!).

[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]

Happy to explain more as you need.

It is a brute force attack so it will get slow quickly as the number of non-zero inputs increase for a row (it's an O(2^N) operation I put in).

[Storm]

JDunkerley:

Please, correct me if I am wrong. But in running through the testing with this flow, it looks like I'm not picking up solutions where just one field equals the total. In other words, 100% of my solutions have 2 or more fields whose sum equal the total; but cases where ONE field equals the total (with no summing needed), I get no results.

I'm not sufficiently in tune with the workings of the flow to understand what I'd tweak. But I am continuing to work on it.

[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 

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!

[jdunkerley79]

that'll do it

[facundosislian]

This is fantastic.
Could you please explain how come IIF(BinaryAnd([ScenarioID],POW(2, [FieldId]-1))!=0,[Value],0) does the trick of for each scenario leaving the exact values that are needed to cover all the alternatives among all of them?
I understand that it transforms the scenarioID into a binary and the POW result into a second binary and compares them like:

    0011 
AND 0010
  = 0010 

So it multiplies the value in the same position of the other number and if there's one digit of the new binary number different from 0 the formula will put the Value of the Field in this scenario.
My question is how do you know that all this calculations are going to take place in order to decide for each field in each scenario if it's going to be 0 or the Value are going to give you perfect scenarios contemplating the different alternatives as it ends up happening.

Regards