Lambda functions to filter datatable

Help
1

Hi

I want to filter a datatable containing rows with dates from this month, previous month and the one before that - result should be only rows from previous months

I would like to avoid for each activity since the rows that are removed are in the datatable same datatable.

I found another post (Delete data row from data table solved by @aksh1yadav ) and tried to make my own solution - but I cant get it to work.

Assign:
PnrSøgtTabel = PnrSøgtTabel.AsEnumerable.Where(Function (x) x.Item(“Navn”).ToString.Substring(5,2).Equals(Ind_FørsteDato.ToString.Substring(5,2))= True).CopyToDataTable

I get the error: startIndex cannot be larger than length of string.
Parameter name: startIndex

The datatable is named “PnrSøgtTabel”
Columnname with the date in the table is named “Navn” - (website is confused)
Sample date from “Navn”: 2019-08-07 08:49:16

Sample confirmation
Write line:
"Måned for Index “+(PnrSøgtTabel.Rows.Count -1).ToString+”: "+PnrSøgtTabel.rows(Convert.ToInt32(row.Item(“antal”))-1).item(“Navn”).tostring.Substring(5,2)
gives
Måned for Index 37: 06

I appriciate your time.

2

Well this error is telling you that you are selecting index that is higher than the length of the string.
What are you trying to select with Substring(5,2).
Maybe try changing numbers 5 and/or 2 with some values like variable.Length, maybe that will resolve that issue

3

Hi,

instead of using Lambda functions which are hard to comprehend you can use a LINQ expression which is more easy to read as SQL expressions.

you should have 3 variables:

  1. dt1 as Source data table
  2. dtResult as destination data table
  3. a random object variable (you can name it as you like because is not actually used)

you create an assign with the to having the object variable and as Value the following expression:

(From s In dT1.AsEnumerable()
Where s(“Navn”)”).ToString.Substring(5,2) = Ind_FørsteDato.ToString.Substring(5,2)
Select dTResult.LoadDataRow(New Object() {s(“Navn”), },False)).tolist()

you need also to initialize the dtReult by cloning the dt1. If you want to extract all the fields then use {s} instead of enumerating all the fields.

With this method you can have also more complex expressions like:

(From s In DTConsolidatedWithIntervals.AsEnumerable()
Join r In DTDatesWithIntervals.AsEnumerable()
On s(“Station ID”).tostring Equals r(“Station ID”).tostring
Where (s(“Date Start”).tostring >= r(“Date_From”).tostring And s(“Date Start”).tostring <=r(“Date_To”).tostring) Or (s(“Date Start”).tostring<r(“Date_To”).tostring And s(“Date End”).tostring>r(“Date_To”).tostring) Or (s(“Date End”).tostring>=r(“Date_From”).tostring And s(“Date End”).tostring<=r(“Date_To”).tostring)
Select DTCentralised.LoadDataRow(New Object() {r(“Date”),s(“User ID”),s(“Car registration”),s(“Station ID”),s(“Date Start”),s(“Date End”),r(“Date_From”),r(“Date_To”),If(r(“Valid From”).tostring=“”,Nothing,r(“Valid From”)),If(r(“Valid To”).tostring=“”,Nothing,r(“Valid To”).tostring),r(“Price per hour (RON)”),r(“Discount per hour (RON)”)},False)).tolist()

Have fun

Also in your example you are trying to perform the operation in the same dt. You should have a different dt for result

PnrSøgtTabel_result = PnrSøgtTabel.AsEnumerable.Where(Function (x) x.Item(“Navn”).ToString.Substring(5,2).Equals(Ind_FørsteDato.ToString.Substring(5,2))= True).CopyToDataTable

4

Thank you @CristianZachiteanu

I do feel it’s simpler to only have one table.

But i can’t seem to get it to work - I’m a bit green in this area - would i need a package (I got studio v. 19.4.4 no packages)?

I’ve tried with the following:
Assign:
PnrSøgtTabel2= PnrSøgtTabel.clone

Assign (Validationerror)
Object=
(From s In PnrSøgtTabel2.AsEnumerable()
Where s(“Navn”)).ToString.Substring(5,2) = Ind_FørsteDato.ToString.Substring(5,2)
Select PnrSøgtTabel2.LoadDataRow(New Object() {s(“Navn”), },False)).tolist()

Validationerror: Compiler error(s) encountered processing expression “(From s In PnrSøgtTabel2.AsEnumerable() Where s(“Navn”)).ToString.Substring(5,2) = Ind_FørsteDato.ToString.Substring(5,2) Select PnrSøgtTabel2.LoadDataRow(New Object() {s(“Navn”), },False)).tolist()”.
End of expression expected.

Also it seems, when rewriting the expression, that tolist() is not a function (not advertised in suggested code when writing it up)

I want the PnrSøgtTabel2 table to include all the columns and data from PnrSøgtTabel - but only some of the rows that matches with the month in Column “Navn”.

Nice collection of expressions you shown me.

5

I’m trying to select rows from a specific date.

I’ve tried to use the substring-part in a write line activity and I get what I’m looking for.

6

hello @Emil.tophoej
you can try below…

Date startDate = CDate(now.addmonths(-1).Tostring("01/MMM/yyyy"))
Date endDate = Cdate(now.tostring("01/MMM/yyyy")).AddDays(-1)

dt = dt.select().Where(function(rw) Cdate(rw("Column1").ToString) => startDate and rw("Column1").ToString) =< endDate).Select(function(rw) rw).CopyToDataTable

This will give all the rows for previous month only
kindly make changes according to your datatable name and column name

7

Hi Emil,

You need to decide which table is the source and which is the destination.

  1. Source: PnrSøgtTabel
  2. Destination: PnrSøgtTabel2

In this case the formula should be:
Assign Temp=
(From s In PnrSøgtTabel.AsEnumerable()
Where s(“Navn”).ToString.Substring(5,2) = Ind_FørsteDato.ToString.Substring(5,2)
Select PnrSøgtTabel2.LoadDataRow(New Object() {s},False)).tolist()

The New Object() {s} will select all the columns available in the dt
The New Object() {s(“Navn”)} will select only the column “Navn” and will insert the result in the first column of the source dt. So if you want to populate only specific columns you need to create the structure of the destination table to match with the selected columns.

see the example attachedMain.xaml (9.7 KB)

8

Thanks for that very neat - I’ve tried filling in som dates and running it - It seems to Work - i just have no Idea of how to treat a object:

  • how to iterate
  • how to call out a specific row/column
  • how to convert to other types
  • how to Count rows

Main.xaml (13.3 KB)

9

Hi,

The object is, as I mentioned before, not used. The result is another DT with which you can do the usual stuff like iterate, cont, select columns and others.

Thank you