Hi, I’m looking for query that can produce the below output.
yah we can use JOIN DATATABLE ACTIVITY
The join data tables activity only supports inner join, left outer join and, full outer join. This person is looking to keep ONLY items from table1 that are not in table2.
@VanithaRajakumar it would be easy enough to write a loop on table1 and compare to table2 and import rows to output table, but I am not sure how to write a linq query for it so I will wait for someone else. Let me know if you’re interested in a loop solution though (would be more than acceptable if there were 100k or less rows I’d imagine)
@Dave… you are right.!! Am looking to keep only items not in Table2. Unfortunately my datatable is more than 200K. So looping might not be a good solution I believe.
@Palaniyappan no because that solution would be the same as a right outer join. In that case the output would be:
And @VanithaRajakumar stated that he wants the out put to be:
@VanithaRajakumar - I would still give the looping solution a try. Perhaps give it a stress test of 300% more rows than usual and see what processing speed you get.
@VanithaRajakumar I just thought of another way to do it that may work and would be quite fast, although it would be done in a few steps instead of a single step
You could combine table1 and table2 into a single table. Then use a linq query to pull out only items where count = 1
@Palaniyappan Inner and Left joins in Join datatable did not work for my requirement
@Dave… You mean to check in loop? Also to mention, the data count will be more than 200k as well. So looking for some query to fetch the rows at once
Got it! This is a linq query that simulates the following sql query
SELECT dt1.* FROM dt1 LEFT JOIN dt2 ON dt2.EmpID = dt1.EmpID WHERE dt2.name IS NULL
The linq query is:
Assign outputTable = (From t1 In dt1.AsEnumerable() Group Join t2 In dt2.AsEnumerable() On t1.Field(Of String)("EmpID") Equals t2.Field(Of String)("EmpID") Into tg = Group From tcheck In tg.DefaultIfEmpty() Where tcheck Is Nothing Select t1).CopyToDataTable()
Here is a .xaml showing it working . Currently it is joining only on employee ID, alter the query with an AND statement if you want to join on more than one field.
LeftExcludeJoin.xaml (10.4 KB)
Thank you very much @Dave . You saved my day. Can you please explain the linq query? I don’t understand this part “Into tg = Group From tcheck In tg.DefaultIfEmpty() Where tcheck Is Nothing Select t1)”. How can I build the linq query myself? The SQL query looks simple where as linq is not
The variable names I chose (t1, t2, tg, tcheck) can be changed to anything. They are just remnants from the various code sequences spliced together haha.
It is grouping the t1 and t2 so you can check if the rows coming from t2 are null/empty
tg.DefaultIfEmpty() and assigns that to the variable tcheck. Then the select statement grabs all of the rows where tcheck is not null/empty
do you have any validation to be used before removing rows or just to remove
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.