Looking Assistant with excel

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:

And Output:


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

Thanks in advanced.
Jesmine

Hi,

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

Sample20220325-1.zip (15.2 KB)

Hope this helps you.

Regards,

1 Like

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!

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

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

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

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

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