Helpful Answers
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.
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.
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.
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.
