3 weeks ago
Hi,
I have a problem that I'm not quite sure how to solve. I will be reading in up to 10 dynamic values in to an collection and out of this I need to combine some or all values to a set sum. The loop variant seems to complex to since it can be over 1000 different combinations. Does any one have a solution - is there a code I could use for combining all the values?
Thanks in advance
Answered! Go to Answer.
3 weeks ago
Let's imagine you have retrieved all your rows in a collection like this :
and your target for instance is 12.
You need to follow these steps :
Go to vbo collection Manipulation and open it.
Then right click here and click new
add a code stage and name it FindCombination and provide these inputs and output like this :
in code stage put this :
Dim outputCollection As New DataTable
outputCollection.Columns.Add("Number", GetType(Integer))
Dim stack(0) As Object
Dim stackSize As Integer = 1
stack(0) = New Object() {0, 0, New DataTable()}
DirectCast(stack(0), Object())(2).Columns.Add("Number", GetType(Integer))
Dim currentStackIndex As Integer = 0
While currentStackIndex < stackSize
Dim state() As Object = DirectCast(stack(currentStackIndex), Object())
Dim currentIndex As Integer = CInt(state(0))
Dim currentSum As Integer = CInt(state(1))
Dim currentCombination As DataTable = CType(state(2), DataTable)
If currentSum = target Then
outputCollection = currentCombination
Exit While
End If
Dim tempStack(0) As Object
Dim tempStackSize As Integer = 0
For i As Integer = currentIndex To nums.Rows.Count - 1
Dim num As Integer = Convert.ToInt32(nums.Rows(i)("Number"))
If currentSum + num <= target Then
Dim newCombination As DataTable = currentCombination.Copy()
Dim newRow As DataRow = newCombination.NewRow()
newRow("Number") = num
newCombination.Rows.Add(newRow)
If tempStackSize >= tempStack.Length Then
ReDim Preserve tempStack(tempStackSize)
End If
tempStack(tempStackSize) = New Object() {i + 1, currentSum + num, newCombination}
tempStackSize += 1
End If
Next
If tempStackSize > 0 Then
If stackSize + tempStackSize > stack.Length Then
ReDim Preserve stack(stackSize + tempStackSize - 1)
End If
For j As Integer = 0 To tempStackSize - 1
stack(stackSize) = tempStack(j)
stackSize += 1
Next
End If
currentStackIndex += 1
End While
combination = outputCollection
Your page in the end should look like this :
Now if i make a test in allnumbers i will add these :
My target number is for instance 12
When i run my code i will obtain this
Ensure that input DataTable (allnumbers) in my case has a column named "Number".
dont forget to add the input in the start stage and the output in the end stage and publish your action to use it in process
Try it and let me know.
3 weeks ago
Hi @emjemed
Can you give us more details.
Dont hesitate to put screen shot because its not really clear for me
3 weeks ago
Hi!
I will try to see if I can make it a bit clear with a print.
I have a system which has rows much like excel (so I will take a print from excel), where different values should create an amount. The values, number of rows and the amount will vary for each case. The rows that does not create the amount should be removed. So in this case all the rows except 4,5 and 7 will be removed. So what is left creates 173. In some case there will be no match for the amount and in some case there are multiple ways to create the amount.
3 weeks ago
Your system is a windows application or web browser application ?
What is the business rules to take a row ?
3 weeks ago
It is a windows application and there is no real business rules regarding removing a row. The only thing is that the remaining rows should be added up to the amount. Which rows is not important for them.
If there is no match to the amount no rows should be removed.
3 weeks ago
Ok,
that mean for example if the amount is equal to 12
And you have 2 3 5 6 1 2 2
You have to take for example 6, 5,1 because the sum is 12 ?
Or you need to take all the combinations that give you 12 ?
3 weeks ago
Exactly, I only need one combination. So it that exempel case yes need 6, 5,1 that makes 12.
3 weeks ago
Let's imagine you have retrieved all your rows in a collection like this :
and your target for instance is 12.
You need to follow these steps :
Go to vbo collection Manipulation and open it.
Then right click here and click new
add a code stage and name it FindCombination and provide these inputs and output like this :
in code stage put this :
Dim outputCollection As New DataTable
outputCollection.Columns.Add("Number", GetType(Integer))
Dim stack(0) As Object
Dim stackSize As Integer = 1
stack(0) = New Object() {0, 0, New DataTable()}
DirectCast(stack(0), Object())(2).Columns.Add("Number", GetType(Integer))
Dim currentStackIndex As Integer = 0
While currentStackIndex < stackSize
Dim state() As Object = DirectCast(stack(currentStackIndex), Object())
Dim currentIndex As Integer = CInt(state(0))
Dim currentSum As Integer = CInt(state(1))
Dim currentCombination As DataTable = CType(state(2), DataTable)
If currentSum = target Then
outputCollection = currentCombination
Exit While
End If
Dim tempStack(0) As Object
Dim tempStackSize As Integer = 0
For i As Integer = currentIndex To nums.Rows.Count - 1
Dim num As Integer = Convert.ToInt32(nums.Rows(i)("Number"))
If currentSum + num <= target Then
Dim newCombination As DataTable = currentCombination.Copy()
Dim newRow As DataRow = newCombination.NewRow()
newRow("Number") = num
newCombination.Rows.Add(newRow)
If tempStackSize >= tempStack.Length Then
ReDim Preserve tempStack(tempStackSize)
End If
tempStack(tempStackSize) = New Object() {i + 1, currentSum + num, newCombination}
tempStackSize += 1
End If
Next
If tempStackSize > 0 Then
If stackSize + tempStackSize > stack.Length Then
ReDim Preserve stack(stackSize + tempStackSize - 1)
End If
For j As Integer = 0 To tempStackSize - 1
stack(stackSize) = tempStack(j)
stackSize += 1
Next
End If
currentStackIndex += 1
End While
combination = outputCollection
Your page in the end should look like this :
Now if i make a test in allnumbers i will add these :
My target number is for instance 12
When i run my code i will obtain this
Ensure that input DataTable (allnumbers) in my case has a column named "Number".
dont forget to add the input in the start stage and the output in the end stage and publish your action to use it in process
Try it and let me know.
3 weeks ago
It worked perfectly, a question is there an easy way to modify the code to also carry an column with rows? If the collection looked as below, that you also got which row the number was associated with.
3 weeks ago - last edited 3 weeks ago
Hi @emjemed
Happy that work for you
No need to code you can make a simple loop with filter to know which item has been retrived 😉
If you have same number for instance 2 2 3 4
It will be the first 2 2 in your main collection.
Regards