I’m trying combine items in a data table and their values in the next cell.
I can disregard the first column, date doesn’t need to be considered at this point.
Example Data table:
Column0
Column1
Column2
Column3
Column4
Column5
Column6
Column7
Column8
Column9
Column10
Column11
Column12
11/1/2021
Task1
4
Task1
3
Task1
1
11/2/2021
Task3
4.5
Task2
1
Task3
8
Task1
3
Task3
2
11/3/2021
Task1
4
Task2
2
Task3
4
Task1
2
11/4/2021
Task1
5
Task3
5
Task2
1
Task1
3
Task3
1.5
11/5/2021
Task1
1.5
Task3
9
Task3
3
Task1
1.5
11/6/2021
Task3
5
11/7/2021
Task1
6
Task2
6
Task1
8
Task1
3
11/8/2021
Task2
5
Task1
3
Task1
1
11/9/2021
Task3
4.5
Task2
1
Task3
6
Task3
1
Example Output:
Column0
Column1
Task1
46
Task2
16
Task3
50
Yoichi
(Yoichi)
November 23, 2021, 11:58pm
2
Hi,
Can you try the following?
dtResult = Enumerable.Range(1,dt.Columns.Count \ 2).SelectMany(Function(i) dt.AsEnumerable.Select(Function(r) {r(i*2-1),r(i*2)})).Where(Function(x) not String.IsNullOrEmpty(x(0).ToString)).GroupBy(Function(x) x(0).ToString).Select(Function(g) dt.Clone.LoadDataRow({g.Key,g.Sum(Function(z) double.Parse(z(1).ToString))},False)).CopyToDataTable
Sample20211124-2.zip (9.7 KB)
Note : The result is not same as your output. Can you check it?
Regards,
It seems to error out looking for a column that is +1 than the columns that exist.
There were 37 columns in data table I had applied this to, and this was the error I received:
Error: Cannot Find Column 38
Yoichi
(Yoichi)
November 24, 2021, 3:18pm
4
Hi,
I tried my workflow with data which has 37columns(this means column0 - column36 as the following sample), and it works without exception. Can you check if there is extra column except target table?
Sample20211124-2v2.zip (15.7 KB)
Regards,
Sorry, I was incorrect before. My data table has 38 columns (column0 - column37).
And the error I receive is “Cannot find column 38”.
Yoichi
(Yoichi)
November 24, 2021, 9:35pm
6
Hi,
In your first post, there is date column at column0 and remaining is pairs of name and value. So number of columns is always odd, isn’t it?
If your table isn’t the same as above structure, can you share it?
Regards,
Thanks! You are correct, I was getting an extra column that was throwing things off. After I avoided that, your solution worked!
1 Like
system
(system)
Closed
November 29, 2021, 8:02pm
8
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.