How to add a whole row to a new collection from an existing collection based on a condition?

#1
Coll1 contains 50 columns and 100 rows, I want to pick the complete row on the basis of condition that Coll1. Column2 & Coll1. Column3 & Coll1. Column4 matches a certain criteria, copy the complete row to a new Coll2 (blank). I know we can copy columns by explicitly mentioning them, but how can we copy the whole row?
 

VJR

Well-Known Member
Staff member
#2
Coll1 contains 50 columns and 100 rows, I want to pick the complete row on the basis of condition that Coll1. Column2 & Coll1. Column3 & Coll1. Column4 matches a certain criteria, copy the complete row to a new Coll2 (blank). I know we can copy columns by explicitly mentioning them, but how can we copy the whole row?
At first you using Copy Rows you can copy x number of rows from the original collection to a temporary intermediate collection (where x is 1 or more).
Then using Append rows to collection, append the temporary collection to a final output collection.
 
#3
Thanks for the reply VJ, as far as I know copy rows will copy x rows without any condition.

I want to copy all the 50 columns in a new collection where col1 = 1 and col2 = 2, which should ideally copy row 1 and row 5 in a new collection with all the 50 columns. I hope I am able to put my query correctly.

col1, col2,col3 .... col49,col 50
1,2
2,1
3,2
4,5
1,2

One solution i thought is to copy the whole collection to new one and then use Remove Row action in loop that doesn't satisfy the condition.
 
Last edited:

VJR

Well-Known Member
Staff member
#4
Thanks for the reply VJ, as far as I know copy rows will copy x rows without any condition.

I want to copy all the 50 columns in a new collection where col1 = 1 and col2 = 2, which should ideally copy row 1 and row 5 in a new collection with all the 50 columns. I hope I am able to put my query correctly.

col1, col2,col3 .... col49,col 50
1,2
2,1
3,2
4,5
1,2

One solution i thought is to copy the whole collection to new one and then use Remove Row action in loop that doesn't satisfy the condition.
Hi,
I was suggesting it to be used in conjunction with a Decision stage. But now that you have given more details you can filter the collection with AND condition on those two columns. This will result into another collection whose rows you can use it to append to another final collection or use it in any other way you need it.
 
#5
Copy row contains Start row and End row what we have to give.

My loop is iterating 2nd row and the condition satisfied. I wanted to copy that to another collection, In that case I have to give start row and end row will be 2 right?
This will be dynamic. based on loop iteration value i have to pass start row and end row value. How to pass that dynamic value to start row and end row.?

Hope we could use counter ? but i m not sure how to use it? guide me on that

View attachment 1544187094416.png
 

VJR

Well-Known Member
Staff member
#6
Copy row contains Start row and End row what we have to give.

My loop is iterating 2nd row and the condition satisfied. I wanted to copy that to another collection, In that case I have to give start row and end row will be 2 right?
This will be dynamic. based on loop iteration value i have to pass start row and end row value. How to pass that dynamic value to start row and end row.?

Hope we could use counter ? but i m not sure how to use it? guide me on that

View attachment 2717
Simply maintain a data item, lets call it Counter.
Whenever you need to increment the Counter use a Calc stage with [Counter] + 1 in the expression and Counter in the Store In field. Now the Counter data item will be increased by 1. You can use this data item in the Copy Rows action.
 
#11
When i used [Data1] = [Data1] +1 --- the result storing as flag. i dont know y.
Check the data type of Data1 , This should be a Number, also set the Initial value of your Data1 Data item as 0. So that when you perform Cal its 0 + some number at the first iteration. The cal stage expression will not return Flag. The Expressiion in the Cal stage shall be your data item + 1. This will return a number which you will store in a number data item
 
#12
Thanks VJ, Filter method works fine, is it possible to use a data item in the filter query, what will be the syntax? I want to filter a collection on the basis of data item "Number" containing a dynamic number.
 
#13
Can some one please assist me with my query?

Is it possible to use a data item in the filter collection, what will be the syntax? I want to filter a collection on the basis of data item "Number" containing a dynamic number.
 
#14
Yes, you can use Data Item in to Filter Collection Filter Input.

Lets say your Data Item is names NumFilter and the column you want to apply filter on is FilterColumn.

In the Filter Input, use "FilterColumn = " & ""&[NumFilter] &""

In case of your Data Item is a of type text use single quotes: "FilterColumn = " & "'"&[NumFilter] &"'"

Post back, if it works!
 

VJR

Well-Known Member
Staff member
#15
Thanks VJ, Filter method works fine, is it possible to use a data item in the filter query, what will be the syntax? I want to filter a collection on the basis of data item "Number" containing a dynamic number.
"OrderNumber = " & [ordnum]

Where OrderNumber is the Number column in the collection
and ordnum is the Number data item.
 
#17
Thanks VJ and SG for the solutions. However, I tried both the solutions, syntax seems to be correct but the resulting collection is empty. I have checked the dataitem exists in column so should result in some data.

Data Item is a number stored as text and the collection column is also a number stored as text. Also there is a space between column name. Any suggestions?

"'Column Name' = " & "'"&[NumberAsText] &"'"
 
#19
Super!! Works like a charm now...

This Filter utility seems underrated. We can use Like, Not Like and regular expressions as well. Do you know any source to refer where I can find more about the syntax and all the possible commands that can be used inside Filter text?
 
Top