Looking Assistant with excel

Help Activities
, ,
1

Hi Beautiful Buddy, @Yoichi @ppr
I am looking assistant from here and believe will get satisfying outcome from here.
Scenario:
I have two datatable dt1 and dt2. So I want to join dt1 and dt2 with dt1 and it would be create dt3 with specific column which column will not match with dt2.
dt1 excel:
image

dt2:

image
image1135×451 11.3 KB

And Output:

image
image1393×398 18.2 KB

Please share your suggestion. If possible then please share dummy flow.

Thanks in advanced.
Jesmine

2

Hi,

In this case we can use LeftJoin in JoinDataTable activity. Then remove unnecessary columns and sort, if necessary.

img20220325-1
img20220325-1977×858 38.1 KB

Sample20220325-1.zip (15.2 KB)

Hope this helps you.

Regards,

1 Like
3

Hello Yoichi!

I did exactly what you said and it was executed perfectly.
All I did was delete the blank item column and convert the column name of item_1 to item.

Thanks very much for the great result.

Thank you!

001
001279×553 3.49 KB

4

Hi @Yoichi,
Thanks! It’s working and I did previously.
I am looking .net or linq functionality…
Can you please assist me?

5

Hi,

For sample in my previous post, can you try the following?

First, define dtResult schema as the following image in advance.

img20220325-1

Then the following will work.

dtResult = (From a In dt1.AsEnumerable
Group Join b In dt2.AsEnumerable
    On a("bill number").ToString Equals b("bill number").ToString
	   Into grp = Group
       From row In grp.DefaultIfEmpty()
       Select If(row Is Nothing,
		   dtResult.LoadDataRow({a("Name"),a("amount"),a("bill number"),a("account"),a("Others"),"",""},False),
		   dtResult.LoadDataRow({a("Name"),a("amount"),a("bill number"),a("account"),a("Others"),row("Status"),row("SUBTOTAL")},False))
		   ).CopyToDataTable

Regards,

1 Like
6

great! If the error comes like source contain no data rows then how I will handle by in this code only?

7

Hi,

If there is possibility it returns no row, first get datarow array using the following expression, then use CopyToDataTable when its length is larger than 0.

arrDataRow = (From a In dt1.AsEnumerable
    Group Join b In dt2.AsEnumerable
    On a("bill number").ToString Equals b("bill number").ToString
	   Into grp = Group
       From row In grp.DefaultIfEmpty()
       Select If(row Is Nothing,
		   dtResult.LoadDataRow({a("Name"),a("amount"),a("bill number"),a("account"),a("Others"),"",""},False),
		   dtResult.LoadDataRow({a("Name"),a("amount"),a("bill number"),a("account"),a("Others"),row("Status"),row("SUBTOTAL")},False))
		   ).ToArray()

Regards,

1 Like
8

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