Multiply datatable rows for each item in another table

I have 2 datatables, one for state populations and one for segment:

State
image

Segment
image

I need the end result to add each customer segment in a new column to each state, essentially multiplying the total number of rows. Final output should look like the below. What LINQ expression can I use to accomplish, without having to use a For Loop?

image

@Greg_Jacobson
prepare a target data table with build datatable - dtResult
Configure all cols from dt1 and the Customer Segment col from dt2

Assign activity
LHS: dtResult
RHS:

(From d1 in dt1.AsEnumerable
From d2 in dt2.AsEnumerable
Let ra = d1.ItemArray.Append(d2("Customer Segment").toArray
Select r=dtResult.Rows.Add(ra)).CopyToDataTable

When I build the dtResults table before that expression, do I include the customer segment column?

Yes, as mentioned above the LINQ will used the cols from dt1, will append the Customer Segment value to the ItemArray, and will use the newly constructed arrayrow for populating the data to dtResult

What if instead of another datatable with Customer Segments, I have those 4 items in an Array? I can’t get it to work with with an array.

the main idea of this approach is to reconstruct the item array. Within the implementations, we try to be as dynamic as possible

please explain it more specific. Thanks

Like this:

(From d1 in dt1.AsEnumerable
From d2 in dt2.AsEnumerable
Let ra = d1.ItemArray.Append({"Home Office","Consumer","Small Business","Corporate"})
Select r=dtResult.Rows.Add(ra)).CopyToDataTable

Doesn’t seem to work

yes append expects only 1 value. we would do:

arrColSet | DataType: String( ) = {“Home Office”,“Consumer”,“Small Business”,“Corporate”}

(From d1 in dt1.AsEnumerable
From d2 in dt2.AsEnumerable
Let ra1 = arrColSet.Select(Function (x) d2(x))
Let ra = d1.ItemArray.concat(ra1).toArray
Select r=dtResult.Rows.Add(ra)).CopyToDataTable

Can we revise that and remove d2 completely? I only have 1 datatable, and 1 array. What would that expression look like?

do one thing:
give clear input sample, along with expected output. We will check for solution approaches.

In case of more general learnings just open a new topic with scope on this. We suggest to keep topics strong scoped to one case only, as mentioned in the topic title. This helps other researchers easier to fin their possible solutions matching to their cases.

Sure, I actually was able to figure it out using my actual data:

(From dt In tempDT.AsEnumerable
Let cases = {"Journal Report","Trial Balance","Journal Entry"}
From cs In cases
Let ra = dt.ItemArray.take(6).append(cs).ToArray
Select r=io_TransactionData.rows.add(ra)).copyToDataTable

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