Grouping numbers by a list of given value

[Paddi]

Hi,

Is it possible to group a list of numbers by specific given value?

For each account (AAA), there will be 2 or more numbers as grouping reference (600,900), the alteryx tool need to decide from input 1 that how should (100,200,300,400,500) be grouped to 600 and 900.

For example, give 600 and 900, 100,200,300,400,500 would be grouped to

100+200+300 =600

400+500 =900

Detailed data, number, output as attached:

Input1: Data to be grouped

Account	Value Date	Amount
AAA	12/1/2023	100
AAA	12/1/2023	200
AAA	12/1/2023	300
AAA	12/1/2023	400
AAA	12/1/2023	500
BBB	12/2/2023	100
BBB	12/2/2023	200
BBB	12/2/2023	300
BBB	12/2/2023	400
BBB	12/2/2023	500
BBB	12/2/2023	250
BBB	12/2/2023	210
BBB	12/2/2023	110
BBB	12/2/2023	190

Input2: grouping reference

Account	Value Date	Goal
AAA	12/1/2023	600
AAA	12/1/2023	900
BBB	12/2/2023	1750
BBB	12/2/2023	510

Output:

Account	Value Date	Amount	Goal
AAA	12/1/2023	100	600
AAA	12/1/2023	200	600
AAA	12/1/2023	300	600
AAA	12/1/2023	400	900
AAA	12/1/2023	500	900
BBB	12/2/2023	100	1750
BBB	12/2/2023	200	1750
BBB	12/2/2023	300	1750
BBB	12/2/2023	400	1750
BBB	12/2/2023	500	1750
BBB	12/2/2023	250	1750
BBB	12/2/2023	210	510
BBB	12/2/2023	110	510
BBB	12/2/2023	190	510

[apathetichell]

can you use words to describe the logic which you are using to aggregate your values? I don't see it.

[Paddi]

Hi @apathetichell ,

I have updated the logic in words, hope it helps. Kindly let me know if any questions.

[CoG]

@Paddi 

The difficulty with this problem is 2 fold, there may be many occasions where multiple solutions exist. How do you handle this? For example, with the input provided:

100+500 = 600

200+300+400 = 900

-OR-

100+200+300 = 600

400+500 = 900

How do you know which to assign? The second issue is more pragmatic. What you are looking for is a solution to a partition problem, which involves permutations, of which there can be many possibilities. Just for the input you provided, there are 540 different ways to group amounts together. Solving this problem can get very big very quickly, which can be slow to run or crash completely.

That being said, if precise grouping doesn't matter, then here is a workflow that does what you're looking for:

There is a macro included (not pictured here for brevity's sake) that computes all possible partitions.

[apathetichell]

is this a number theory experiment? What's the context here? As @CoG succinctly pointed out - and I ambigously referenced - your rules do not work because there are multiple potential combinations.  You can try to experiment using the optimizatoin tool for some of this stuff. But I'm not really sure what you're looking to do based upon your descriptoin.