Data Select Help - unfilteredDT.Select("['EmailTP'] = 'athleteEmail'").CopyToDataTable

Help
1

hi!

have gone through multiple threads and videos before posting.

i am stucking in being able to use select to filter a datatable column to filter using a for each variable that is equal to an email.

right now i have an assign with

filteredDT = unfilteredDT.Select(“[‘EmailTP’] = ‘athleteEmail’”).CopyToDataTable

EmailTP is text column name for a DT

athleteEmail is a string variable.

thanks in advance for any help.

r

2

@Raul_Santaella

It should be like this:

             filteredDT = unfilteredDT.Select("[EmailTP] = ‘ "+athleteEmail+" ’ ").CopyToDataTable
1 Like
3

Hi, lakshman
I am new to the Select statement. Would appreciate if you could explain the syntax of the Select expression you provided. I know that you need to specify the datatable before the Select statement. Based on the syntax you provided, looks like you specify the Column header which is EmailTP and when specify the specific column value looking for which is athleteEmail, you need to put that in quotation mark (') and also “+”. Would like to check if the following syntax is correct if I want to select rows with either column value =“athleteEmail” or “cricketEmail”:
filteredDT = unfilteredDT.Select("[EmailTP] = ‘ “+athleteEmail+” ’ ", ‘ "+cricketEmail) '.CopyToDataTable

1 Like
4

@Bee1

Try this query

filteredDT = unfilteredDT.Select("[EmailTP] = ‘ “+athleteEmail+” ’ " or "[EmailTP] = ‘ “+cricketEmail+” ’ " ) '.CopyToDataTable

5

@Bee1

Basic syntax of select statement is:

DatatableName.Select("[Column1] = ‘Value’ AND/OR [Column2] = ‘Value’ "…).CopyToDataTable

As you said you want to pass variable into select query. So, try this:

filteredDT = unfilteredDT.Select("[EmailTP] = ‘ “+athleteEmail+” ’ OR [EmailTP] = ‘ “+cricketEmail+” ’ ").CopyToDataTable

6

Thanks, lakshman
i have better understanding now. Would like to check if the select sentence I constructed below for this scenerio is correct [Scenerio: Column, “outstandingDay” shows the number of days email for a certain person is outstanding for x days; Column, “outstandingDay” is an integer column and I want to select any emails outstanding > 3days.

filteredDT=unfilteredDT.Select("[outstandingDay]>‘3’).CopyToTable

1 Like
7

@Bee1

Yes it’s correct but double quotes missing at the end. And also it CopyToDataTable but not CopyToTable

It should be like this.

filteredDT=unfilteredDT.Select("[outstandingDay]>‘3’ ").CopyToDataTable

8

Noted and many thanks, lakshman. I am learning a lot. I got some more questions.

If I want to filter and copy all rows belonging to each “athleteEmail” with outstandingDay > 3 to a data table, i.e. each athleteEmail will have one datatable, e.g. guna@gmail.com with 2 data rows with outstandingDay>3 will get a database table showing these 2 rows and ram@gmail.com with 3 data rows with outstandingDay>3 will get a database table. How should I structure the select statement for this scenario.

1 Like
9

@Bee1

Try this:

               newDT = DT.Select("[EmailTP] = 'guna@gmail.com' AND [outstandingDay] > '3' ").CopyToDataTable
              newDT1 = DT.Select("[EmailTP] = 'ram@gmail.com' AND [outstandingDay] > '3' ").CopyToDataTable
10

Thanks, lakshman. The expression you provided look for specific EmailTP. What I am looking for is to filter and select all duplicate EmailTP rows > 3 days and to copy each of these duplicate EmailTP to a datatable each.

1 Like
11

@Bee1

Could you please help me with sample input and output data. So that i can help you better in this.

12

Thanks, Lakshman!

Attached is the sample input file which is dynamic and will change every week. Attached is the output files for outstandingdays>3. There is 1 outputInputs.xlsx (8.3 KB) OutputReminder-Guna.xlsx (8.3 KB) OutputReminder-Ram.xlsx (8.1 KB) OutputReminder-Ramsey.xlsx (8.1 KB) file for every unique username with outstandingdays>3.

13

thanks a lot.

so so i can move forward understanding the syntax. i incorporated the plus signs from another post in this forum.

if i am to use .Select() then:

insert double quotations to begin and close the statement.

use brackets outline the column name even if it is in text.

if i am using a criteria to filter i need single quotation

if that value is a variable the double quotation is for the text in that variable

the plus sign does ???

following this copytodatatable

how to i create a new excel file with the variable name. i am a new user so i am not able to post my workflow. my issue with this has been selecting a for each flow to use the email to filter and create a new file. in the future after this new athlete email file name is created i would just append to the file.

thanks for your help. greatly appreciated.

r