Merge column in Datable & calculate value (LINQ)

I have datatable as below.
image

But I want data in dataTable edit datatable as below.

Please guide me about it.

Example Data as attached.
Edit Datatable.xlsx (11.3 KB)

Hi @fairymemay ,

The second image hasn’t been uploaded, could you please upload it once more?

Kind Regards,
Ashwin A.K

@ashwin.ashok upload image completed.

Hi @fairymemay ,

Could you give this a try?

(From row In dt_sampleData.AsEnumerable()
Let coname = row("Name1").ToString +" "+row("Name2").ToString
Let apsum = Convert.ToDouble(row("APSUM").ToString)/Convert.ToDouble(row("Case").ToString)
Let fyp = Convert.ToDouble(row("FPP").ToString)/Convert.ToDouble(row("Case").ToString)
Let ra = New Object() {row("no"),coname,"",apsum,row("Case"),fyp}
Select dt_result.Rows.Add(ra)).CopyToDataTable()

CombineColumns.xaml (7.5 KB)

Kind Regards,
Ashwin A.K

@ashwin.ashok In real file after column FYP , I have column 10 column and before column APSUM , I have 2 another 2 column.

Please guide me for edit your code.

Hi @fairymemay ,

Lets assume that you have 10 columns before and after the columns you have specified so far.

10cols <-[no],[name1],[name2],[apsum],[case],[fyp]->10columns

You can try this →

Let ra = row.ItemArray.Take(dt_sampleData.Columns("no").Ordinal+1).Concat({coname,"",apsum,row("Case"),fyp}).Concat(row.ItemArray.Skip(dt_sampleData.Columns("no").Ordinal+1+5).ToArray).ToArray

Kind Regards,
Ashwin A.K

@ashwin.ashok I have a question.

What mean +1 , +5 ?

Hi @fairymemay ,

The +1 is since the Ordinal(Column index) starts its index from 0, so to get the actual number of columns we require, we have to add one.

The +5 refers to the Columns that we have specifically made changes to and have been concatenated to the itemarray. Since they have been added, we are skipping past them and adding the rest.

Kind Regards,
Ashwin A.K

@ashwin.ashok Can you edit your code to linq ?

I think it flexible to me (because not fix number column)

Hi @fairymemay ,

Sure no problem, could you try this and see if it works?

(From row In dt_sampleData.AsEnumerable()
Let coname = row("Name1").ToString +" "+row("Name2").ToString
Let apsum = Convert.ToDouble(row("APSUM").ToString)/Convert.ToDouble(row("Case").ToString)
Let fyp = Convert.ToDouble(row("FPP").ToString)/Convert.ToDouble(row("Case").ToString)
Let ra = row.ItemArray.Take(dt_sampleData.Columns("no").Ordinal+1).Concat({coname,"",apsum,row("Case"),fyp}).Concat(row.ItemArray.Skip(dt_sampleData.Columns("no").Ordinal+1+5).ToArray).ToArray
Select dt_result.Rows.Add(ra)).CopyToDataTable()

Kind Regards,
Ashwin A.K

@ashwin.ashok If I don’t want use +1 , +5 but I want use column name only.

Can do it?

Hi @fairymemay ,

The +1 is since the Ordinal (Column index) starts its index from 0, so to get the actual number of columns we require, we have to add one.

As explained above, the indexes are not referencing the columns.
We are using Ordinal since we are referencing the columns by its name as in here →

dt_sampleData.Columns("no").Ordinal+1

It is only that to account for the displacing we are doing as we update the row items.
Also, I wanted to confirm if the logic is working, could you please do that?

Kind Regards,
Ashwin A.K

@ashwin.ashok If real header as below.

Header.xlsx (9.9 KB)

How to edit your code?

@ashwin.ashok Now I use code as below.

DT_TextFile.AsEnumerable().ToList().ForEach(Sub(row) row("Name1")= row("Name1").ToString+" "+row("Name2").ToString)

For concetenate column Name1 & Name2.
But I don’t know how to edit code for calculate in column APSUM and FPP?

Please guide me for edit my LINQ?

If you just did it with regular activities you’d be done already. This is a very simple thing to do with regular activities. Why are you trying to use LINQ?

@postwick because​ my​ data​ about 200K rows.
If use for each​ row​, I think it slow.

This is a myth. For Each is not slower. In fact, LINQ can be slower because it introduces additional overhead. Also, LINQ does not avoid looping. It has to loop just like anything else. It just makes it hidden.

Hi @fairymemay ,

I apologize for the late response.

It seems as though that the columns themselves are spread out all over the place, so we can’t use this code.

I’ve developed another one instead, which you can use inside of an Invoke Code Activity like so →
image

dt_sampleData.AsEnumerable().ToList().ForEach(row =>{
row["Name1"] = row["Name1"].ToString()+" "+row["Name2"].ToString();
row["APSUM"] = Convert.ToInt32(Convert.ToDouble(row["APSUM"].ToString())/Convert.ToDouble(row["Case"].ToString()));
row["FPP"] = Convert.ToInt32(Convert.ToDouble(row["FPP"].ToString())/Convert.ToDouble(row["Case"].ToString()));
});

This way, we can remove the dependency on the column indexes.
I’ve included a workflow to make it easier for you, please have a look and let me know if it works as expected.

CombineColumns(InvokeCode).xaml (7.8 KB)

Kind Regards,
Ashwin A.K