Select columns and add repeated values

Do you use exactly the file you provided as example?

I suspect the 4th column is type String and following code therefore fails
gr.sum(Function(x) CDec(x.Item("IBCS")))},

You may try
gr.sum(Function(x) CDec(x.Item("IBCS"))).ToString},

Cheers

Doesn’t work! :sleepy:

image

@Beatriz_Eugenia_Duqu
as the groupby LINQ in general looks fine lets analyse the failing Conversion along with the data.

May we ask you to do following:

  • set a breakpoint before the assign with the LINQ
  • run in debug mode and get paused by the breakpoint
  • open the watch panel
  • type in:
YourDataTableVar.AsEnumerable.Select(Function (x) x("IBCS").toString.trim).Where(Function (d) Not Decimal.TryParse(d,nothing)).toList
  • share the result with us

so we can analyze the failing values more in detail

image

I don’t quite understand what you’re saying

@Beatriz_Eugenia_Duqu
Similar like this:
Set Breakpoint before Assign usinf the LINQ Statement

Debug and wait till its paused
Open watch panel / immediate and type in the statement from above in Enter, Enter:
grafik

And in this case we do see: a row with the column Value “A” is in the data and let fail the conversion


This would be the output

This is a good news
Now lets do a quick crosscheck:

YourDataTableVar.AsEnumerable.Select(Function (x) x("IBCS").toString.trim).Where(Function (d) Decimal.TryParse(d,nothing)).toList

the Not is removed and we do look if values are returned

I´m work withGroupByAndSubtotal (1).xaml (6.7 KB) this Workflow

It looks like you have blanks in the IBICS cols, is it?
grafik

the content of the datatable is to check not EXCEL

Also it seems that a different Excel as above is executed
The LINQ from the last provided XAML by you is not implementing all latest revisions

So I would recommend: Bring your XAML on the latest Code Base, run it with small, well validated sample data set and ensure the dt2 Column Datatypes

for the last point you can go for datatable done with build datatable activity instead to do the clone. So you have the column datatypes under your control

image
Not, also has a numeral format, thousands are separated with a comma and decimals with a point


I made a mistake in the first image, sorry

Note that you placed .ToString in wrong position
image

It should be like this
gr.sum(Function(x) CDec(x.Item("IBCS"))).ToString}

Cheers


This is my output

@Beatriz_Eugenia_Duqu
when you compare the different LINQs and Exceptions you will notice that it had

  • cast issues
  • conversion not valid string issues

the field(Of String) part should be changed as mentioned above. So please change:
row.Field(Of String)(“CEDULA”) to row("CEDULA").toString

Running it again we would expect again issues of type not valid string conversions
Retyping the check for unparseable values would return a non empty result

and if it is coming like this we would interpretate it, that these empty fields are causing the issue

This is the output

as expected and mentioned above

But then as I can solve it, in fact in the file of excel as I show him he/she doesn’t bring empty fields, additionally, in fact I should filter the data and in the cases in that the card is repeated I should add the values of the IBC that correspond to the card that repeatsPrueba 1.xlsx (12.0 KB)
Please help
Sincerely thanks
Regards,

Right. Looks like some “IBCS” values are empty. I simulated this scenario and got same error…

Cheers