UiPath | LINQ Join Function | Inner Join | Left Outer Join | Right Outer Join | Full Outer Join

[@TheOriginalbeat]

Well explained as usual, great video Mahmoud.

[@ActAutomate]

Thanks Mohamed :-)

[@fwziayasser4510]

Great video as usual Mahmoud.

[@ActAutomate]

Thanks Fwzia 😀

[@nehalakhani5638]

Thank you! It was easy to follow

[@ActAutomate]

You're welcome Neha :-)

[@asramaa]

Thank you.

[@ActAutomate]

You're welcome :-)

[@Dinesh-ql1ok]

Amazing tutorial!

[@ActAutomate]

Thanks Dinesh 😊 You can also check the other LINQ videos about other functions. It will also help you further, if you want to learn LINQ and you are a beginner. There are many videos about the most used functions in LINQ!

[@dimasramadhan8182]

very clear and easy to understand.. mantaps

[@ActAutomate]

Happy to hear that, thanks for commenting Dimas :)

[@DrzymalaWagon]

These could the best tutorial vids on more advanced UiPath topics. Only wondering whether there is a simpler Full Outer Join query than doing both Left and Right before hand.

[@ActAutomate]

Ja sure you can do the full outer join without left outer join and right outer join. In UiPath there is the activity: Join Data Table. You can use the option Full Join. But in my video I used the left outer join and right outer join because I have the both already in my code, therefore I don't have effort.

[@saiprasadkakumanu521]

Hi is there any Video on Max, Min functions and some other Math functions.. not sure so many Videos so pls share link or let us with one example on Math functions

[@ActAutomate]

There is currently only a video about Sum function. Here is the link: kzbin.info/www/bejne/lZyyfGmHYruDjNk But on 09.11 the new video about Min, Max, Sum, Count, LongCount, All, Any, Average will be online. The video is called Aggregate. Just wait for 09.11.

[@tarunarishumehra5976]

I am getting this below error - Object reference not set to an instance.

[@ActAutomate]

You have an error of both: Either the query doesn't return any rows, because there are no matches. In this case you have to check the video: Filter DataTable LINQ. In this video I explained how to avoid such exceptions. Or you didn't initialize the DataTable variable before executing the query. So, you need to define the table and the columns of the table before executing the query, else you can not add the data you want to select from the query. Please check both and let me know if you still have the exception. Easier will be if you tell me, which activities and which query you have.

[@khaikit1232]

Hi sorry I can't really understand and visualise group join, how it works and what it does to the data. Would you be covering this in future videos?

[@ActAutomate]

Sure, we will do that :)

[@khaikit1232]

Thank you!

[@ActAutomate]

@@khaikit1232 Any time!☺

[@santiagotabares772]

Hello, In left join, is it possible to return all the columns without having to name them one by one?

[@girishkrishna4654]

CStr(a("ID")).Equals(CStr(b("ID")) Mahmoud, Why didn't this condition work in inner join?

[@ActAutomate]

It should work, but maybe because of the data you have inside the tables. You can use one of these three conditions: 1. a.Field(Of String)("ID") Equals b.Field(Of String)("ID") 2. a("ID").ToString Equals b("ID").ToString 3. CStr(a("ID")).Equals(CStr(b("ID"))

[@yashobantadash6670]

when we group them, every column should be inside "g" variable right? why we are taking a(col name), a(col name) and g(col name). cant we add the rows into data table with g(col name). confused here bro in right and left join

[@ActAutomate]

Hi Yashobanta, normally we can also use g variable to get the content of the columns. You can try it and check the result. I didn't try it but it should work. If it doesn't work, then it's because the following: In the tables we have the data we need. For example the first table on the left side contains all rows. The second table on the right side doesn't contain data for each row from the first table. Once we join (Left for example), we already have data from the first table, but no data from the second table. Therefore we use the variable to get the data from the first table, and g to get the data instead of the second table. But I think g would be ok to get data from both tables, because it will contain all data. This is a good point, what I didn't thought about before. I will try it and check the difference and let you know, if we have differences between both options!

[@yashobantadash6670]

@@ActAutomate thanks a lot bro..😊

[@sangitaandhale1673]

Thanks for your video.. I have similar requirement need to compare both excel files having same col PersonId & creation date so i need to check both file having same PersonId if yes then check the creation date against same PersonId which is created first or earliest then mark status as new column in result file against same PersonId & creationdate- Own by Excel1 or Excel2

[@sangitaandhale1673]

Plz help me to create linq query for the same requirement

[@ActAutomate]

I already explained that in this video, and also in other videos. If you still need help, please write me more details about your request, so that I can help you further. You can send me the Excel files per Mail and explain the request, and I will send you the solution.

[@sangitaandhale1673]

@@ActAutomate i sent you mail from my id along with input and output file.. looking forward to hear from you thanks a lot

[@ActAutomate]

@@sangitaandhale1673 I sent you the solution per mail. Here is the LINQ query again: ( From a In DT_1 Join b In DT_2 On a("PERSON_ID").ToString Equals b("PERSON_ID").ToString Let aDate = DateTime.ParseExact(a("CREATION_DATE").ToString, "yyyy-MM-dd HH:mm",System.Globalization.CultureInfo.InvariantCulture) Let bDate = DateTime.ParseExact(b("CREATION_DATE").ToString, "yyyy-MM-dd HH:mm",System.Globalization.CultureInfo.InvariantCulture) Select Out_DT.Rows.Add(a("PERSON_ID").ToString,a("PERSON_NUMBER").ToString,If(aDate>bDate,a("CREATION_DATE").ToString,b("CREATION_DATE").ToString),If(aDate>bDate,"PersonNo Own By Excel1","PersonNo Own By Excel2")) ).CopyToDataTable

[@NandhivardhanReddy]

In Left outer join code, at On clause How do i add two conditions with Or operator Note: And operator is working but not Or operator. Any solution ?

[@ActAutomate]

Hi Nandhivarhan, Unfortunately there is no way to do that using OR operator, because here we must have a key, that connects both tables together. You can only use And & AndAlso. But Or & OrElse are not allowed to add here. You have to do that in another way, but in "ON" you can not use OR operator. Maybe you can use the same query twice. Each one using one condition. After that you can merge both results together and remove the duplicates. This will be a simple solution.

[@NandhivardhanReddy]

@@ActAutomate Thanks for your engagement

[@vikrampurohit3033]

Why you are creating build data table each time, could you please explain it.

[@ActAutomate]

Because I want to add the rows to the output table. And I should have columns in this table, because I am using the following statement: out_DT.Rows.Add({HereIPutTheDifferentValuesForEachColumn}) So we need to have columns, else we will get an exception. You can use Build DataTable activity. Or you can also use something else: Clone Function, if your columns are the same as your input data table For this you can add assign activity: On the left side you put: out_DT And on the right side you put: Excel_DT.Clone In this way you will have the same columns in out_DT as what you have in Excel_DT But if you need specific columns, new columns, only one column, etc. you need Build Data Table activity, so that you can add any columns you need I hope it's clear now. Please write us back, if you still don't understand it

[@vikrampurohit3033]

@@ActAutomate understood it very clearly, thanks for immediately response, thanks for your videos…..it’s very informative

[@ActAutomate]

@@vikrampurohit3033 Any time!

[@vikrampurohit3033]

@@ActAutomate Do you have any other videos for Full outer join...

[@ActAutomate]

@@vikrampurohit3033 ​ Under creation! Need time to do it, but no worries, it will be available in the next weeks!

[@devashishnigam5971]

How do we do if we have more than 1 column as key for joining dataTables

[@ActAutomate]

You can only add the second column with AND function. For example: You key columns are ID and Name from both tables --> You set the statement so: On a("ID").ToString.Trim Equals b("ID").ToString.Trim and a("Name").ToString.Trim Equals b("Name").ToString.Trim I hope it is clear for you

[@muneeralrashdan6446]

hi♥ I have an excel file containing columns (Emp No)(Clock In)(Clock Out) Column (Emp No) contains employee numbers How can I separate each employee (by employee number) in the -Emp No- column on a separate sheet A copy of the Excel file for clarification: drive.google.com/drive/folders/1AmvW3pyna968OO4pGnY-TbEZ0gGNl3If?usp=sharing

[@ActAutomate]

Hi Muneer, Do you mean, you want only to have the employee numbers on one sheet without any other information or do you need all columns for one employee on one sheet? I mean these two options: First one: You want to have on a new sheet only one column, which contains the employee numbers (without duplicates) Second one: You want to have for each employee one sheet. On this sheet you want to have the information for this employee. Which option do you mean or do you mean something else? Do you want to do that using LINQ or in a code stage (Invoke Code) or using UiPath standard activities?

[@muneeralrashdan6446]

@@ActAutomate hi The second way (Second one) The method used does not matter

[@asramaa]

Thank you.

[@ActAutomate]

You're welcome :-)