How to combine two columns into one

Hi guys,

In some part of my code i have to combine two columns into one… The logic is the following:

  • if i have some value in “column50” and no value in “column51” then keep the value
  • if i have some value in “column50” and some value in “column51” then keep the value from “column50”
  • if i dont have a value in “column50” and have value in “column51” then pass it to “column50”
  • if i dont have a value in “column50” and have no value in “column51” then keep it empty

image

So my question is… Is there a way of doing this not using the “for each row” activity? is there something more efficient that i can use?

I hope i made myself clear, thanks in advance… :grinning:

@mateotabares

Interesting :slight_smile:

How about reading both column 50 and 51 in separate datatable and then using JOIN activities.

Not sure but , Right outer join can work…


Mukesh

Hi @mukeshkala

I only have 3 types of joins in the “join datatable” activity and none of them seems to work…

image

Is there other way of doing that specific type of join?

@mateotabares
Can you please following Statement If IT is covering your requirements:

If col51 has a value then Pass it to/Overwrite with this value col50

After done with this for all rows, then delete col51 from Datatable

Is this a correct understanding?

[quote=“mateotabares, post:1, topic:176521, full:true”]

NOTE:- BELOW STATEMENT DOES NOT SAFISFY IN THE IMAGE THAT YOU HAVE SHARED. PLEASE CONFIRM THE CORRECT LOGIC.

  • if i have some value in “column50” and some value in “column51” then keep the value from “column50”

image

@hacky

Youre right… im sorry…

the correct statement is this:

  • if i have some value in “column50” and some value in “column51” then keep the value from “column51”

@ppr Hi

“If col51 has a value then Pass it to/Overwrite with this value col50
After done with this for all rows, then delete col51 from Datatable
Is this a correct understanding?”

yes @ppr , thats just what i need… do you see it possible not using the “for each row” activity?

@mateotabares
I will check after my Work shift in the evening and let you know the result. On the first look a compact implementation should be possible

:rofl:again all against the For Each tho it would be so much easier to do…

I literally just implemented this exact same logic in a Robot on Tuesday. I did it with the “for each row” solution and IF Activities.

That said, I want to try something else, because I think it can be done via Invoke VB Code activity : Something like:

Array.ForEach(yourtable.Rows, Function(row) row(“Column50”) = row(“Column51”).ToString.Length > 0 ? row(“Column51”) : row("Column50))

Of course this is also iterating over the rows running the function on each row. If you have multiple “update” column pairs So column51 overwrites column50 if column51 has data, AND column53 overwrites column52 if column53 has data, then you modify the function to update both columns.

This will be more compact, but not really more efficient.

Even if we use LINQ to only select rows where Column51 has a value, and then iterate over them to perform the update, we would still be iterating over the table to select the rows, and then iterating over the results to perform the update. Still O(n) time complexity, so performance will be pretty much the same.

Bottom line is, I do not thing there is a better performing method to implement this. Unless your table is huge, all the methods should be pretty fast.

If your table is huge, it should be in SQL Server (or another database), and you use a SQL update statement.

– Michael.

The only faster way to do, besides the for each activity would be invoke code using a simple For using the indexes, but, tbh we are using uipath for a reason, trying to use the least amount of code possible…

@mateotabares

I worked out 3 different approaches, for demonstrating the no for each option

Approach1 - for each
grafik

It is standard, nothing wrong with it and it is beautiful

Approach2 - for each combined with LINQ
grafik

the if else were saved. But it is important that the dev can handle it. Then we can combine standards with some short cuts

Approach 3: the one liner
grafik

it looks like a without for each solution, but a look on the longer statement shows: The LINQ statement also uses a for each statement. But here we go. If only a minority in the dev team can handle, then we have a bottleneck. Its a cool thing, but has Pros and Cons.

So feel free to use the approach of your choice. Demo XAML is here: mateotabares_2colTo1.xaml (14.8 KB)

Let me know your open questions. Mark the solving post as solution, so others can benefit from it

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.