Read Collection Column name w.r.t ROw

[SaumitraSharma]

Hi,

I need one help, I have collection in that collection i have to read the column name of the row which is having value "O" in collection row.

Also my input file column has dot and dash also in the column names.


------------------------------
Saumitra Sharma
Consultant
Wipro Digital
Indian/Christmas
------------------------------

[jorge.barajas]

Hello Saumitra, 

Can you please clarify if you are using the Excel VBO to read from a file or the Collections VBO to read a field in your collection? Your post suggests you are using both a collection and you are working with an input file but it does not specify if it is a text file, csv, excel or other. Also it does not specify if the column name
w.r.t' is located in the file or in the collection.

Can you please add more details about your situation, what you are trying to accomplish and maybe post some screen shots of your configuration so we can help a bit more?

Thank you!

------------------------------
Jorge Barajas
Blue Prism
Senior Product Consultant
Austin, Texas
------------------------------

[SaumitraSharma]

Hi Jorge,

I have Excel input file, I am using OLE DB to load the data from excel to collection, Post loading the data in collection from excel, now i have to check collection where "O" is present once found i have to read the collection column name for that.

------------------------------
Saumitra Sharma
Consultant
Wipro Digital
Indian/Christmas
------------------------------

[jorge.barajas]

Thank you for the additional information. A few additional questions so I can understand your situation better please:

1. How many columns and rows are present in your file/collection? Can you please post the file here so I can take a look at the file and all the values you are working with?
2. Are you trying to find every column name that has the exact value of "O" or every column that contains the letter "O" in its value or only certain occurrences?
3. Can you provide a little bit of context regarding you are following this approach to solve your problem?

This info will help so I can understand what options are available. 
Thanks,

------------------------------
Jorge Barajas
Blue Prism
Senior Product Consultant
Austin, Texas
------------------------------

[SaumitraSharma]

Hi Jorge,

Please find below my response.

1. The total number of column in my input file is 255 and n numbers of row, I can't provide you my input file as it's having confidential data.
   For the better understanding i have created a dummy collection same like my requirement please find the attahcment.

2. I am trying to find out the only column name which has "O" letter in it's value.
3. My approch to solve the problem is , To run a loop and store [collectionname.fieldname] value in one data item and check the value if it's letter "O" then processed further however as i mentioned i have around 255 column in my excel input hence this solution is not feasible.

My last option is code stage which i don't prefer, My prefrence to resolve the issue using tool functionnality.

Let me know if you need any further information


------------------------------
Saumitra Sharma
Consultant
EY
Indian/Christmas
------------------------------

[jorge.barajas]

It sounds like you are trying to:
1. Loop through the Excel Data Collection one row at the time, and then

• for each row in the collection, evaluate each of its 255 column values
  
  •  If the column value is "O" then
    • find the column name associated to that column value and
    • add it to a separate collection (that will contain only the column names that contain "O" values)

Is this correct? It sounds like your goal is to have a collection of column names that contain "O" values. I say that because the image above shows that multiple records can contain "O" values. 

It also looks like you are trying something similar to the pseudo-code I listed above, can you explain why you mention that this approach is not feasible for you?

Thanks for working with me and providing all the details requested.

------------------------------
Jorge Barajas
Blue Prism
Senior Product Consultant
Austin, Texas
------------------------------

[SaumitraSharma]

Hi Jorge,

Thanks for your prompt responses.

I am trying the same solution which you mentioned in your reply, Loop through all the column and store that value in one data item if that data item have "O" then store the value of collection column name, However why i am saying it's not feasible because when we run the loop on collection and store the collection value we need to write as [Collection.FieldName] and run the loop one by one, In my case i am getting 255 column from my input excel to collection in that case i have to write 255 times [Collection.FieldName] hence i said it's not feasible.

I am looking for solution where my code will be dynamic and i don't need to write 255 times, Only i should pass "O" as a input and  get the column name as a output.





------------------------------
Saumitra Sharma
Consultant
Wipro Digital
Indian/Christmas
------------------------------

[NicholasZejdlik · Accepted solution]

While a code stage might be the easiest way of going about this, I believe this is doable with the Utility - Collection Manipulation actions. Broken down into two parts, the first part will filter the rows, and the second part will filter the columns.

To filter the rows:

1. Call the Get Collection Fields action. 

You should now have a collection containing the column names. If we use the example collection in your other post, this new collection would contain three rows with "Column1", "Column3" and "Column2".

2. Loop over the column names. Using a text data item, concatenate the column names with a filter for 'O' and add an OR.

After this step, you should have a big honkin' text data item that contains text that looks like this:

[Column1] = 'O' OR [Column2] = 'O' OR [Column3] = 'O'

3. Call the Filter Collection action, passing in the original collection and the filter from step 2.

After this step, the collection should be filtered so it will only have rows with 'O' as a value.

To filter the columns:

1. Loop over the column names again, then:

    2. Filter the collection using just the individual column name (so [Column1] = 'O').

    3. Count the number of rows. If the row count is zero, then that column does not contain any rows with 'O'. Call the Delete Field action to remove it.

If you run both parts, you should have a collection containing just columns that contain the letter 'O' in one of their rows, and only rows that contain the letter 'O' in at least one of their columns.

------------------------------
Nicholas Zejdlik
RPA Developer
------------------------------

[jorge.barajas]

Hi Saumitra, 

The mechanism you describe sounds to me like the behavior of a dictionary data structure. You could write a VBO that provides the mechanisms of a dictionary using Blue Prism logic or write a code stage, whichever option is more viable to your case and that can be maintained long term by your team. 


------------------------------
Jorge Barajas
Blue Prism
Senior Product Consultant
Austin, Texas
------------------------------

[SaumitraSharma]

Hi Jorge,

I have used code stage(C#) to achive this functionlity, It's working fine. No nned to run any loop or anything, Simply input the collection and find valoue "O" and in output i am getting the column name for the "O".


------------------------------
Saumitra Sharma
Consultant
Wipro Digital
Indian/Christmas
------------------------------