aliaga
(Ali Aga Mustofa)
November 21, 2019, 11:09am
1
Hello,
I have some problem filtering datatable using filter sequence. so i have datatable from excel, i have to filter it by range date from ‘01/08/2019’ to ‘20/11/2019’.
Column Filtered :
is filter datatable sequence solve all my problem? or other sequence/code?
Thankyou
1 Like
Filter data table activity will work.
Please use between in the filter condition.
DateCol date1 BETWEEN date2
Regards,
Karthik Byggari
samir
(Samir Mohite)
November 21, 2019, 11:30am
3
Hi @aliaga ,
I’ll suggest you to use following code in Assign (After Read range)
Dt1 = Dt1.Select("Letter Date >= #08/01/2019# AND Letter Date <= #11/20/2019#").CopyToDataTable
here,
Dt1 is output variable of read range activity.
aliaga
(Ali Aga Mustofa)
November 22, 2019, 3:35am
4
Hello @KarthikByggari ,
i already use this but it will return 0 rows of datatable.
my variable :
samir
(Samir Mohite)
November 22, 2019, 4:42am
6
Hey @aliaga ,
Use this (I’d missed &’ ')
dt.Select("[Letter Date] >= '#08/01/2019#' AND [Letter Date] <= '#11/20/2019#'").CopyToDataTable
This is working on my side. check it and let me know.
1 Like
It won’t work here, because your start and end dates are strings here.
Change start and end dates to DateTime variables and try again the filter data table activity.
And also try the expression given by @samir
In the expression, if column name has spaces we need to enclose column names in square brackets. [Column Name]
Regards,
Karthik Byggari
samir
(Samir Mohite)
November 22, 2019, 5:34am
8
@aliaga
Use code in my previous post and If you need i’m attaching sample workflow for your reference.
Example.zip (21.0 KB)
1 Like
aliaga
(Ali Aga Mustofa)
November 22, 2019, 6:55am
9
Hello @samir ,
i’ve try use your sequence and it will return this error :
samir
(Samir Mohite)
November 22, 2019, 7:12am
10
@aliaga ,
Check the dates in assign filter,
Example .
if the filter dates range is 1st Jan to 10th Jan and there’s no date falling in the specified range.
1st Feb
23rd Feb
30th March
then it is obvious that, you’ll get this type of error, showing Contains no DataRows .
So kindly check dates in filter and make sure you give 'em in MM/dd/yyyy format.
aliaga
(Ali Aga Mustofa)
November 22, 2019, 7:13am
11
Update,
using this sequence work :
@aliaga
Use code in my previous post and If you need i’m attaching sample workflow for your reference.
Example.zip (21.0 KB)
My Mistake is didnt change the column format in excel to date.
Thankyou for @samir and @KarthikByggari
1 Like
aliaga
(Ali Aga Mustofa)
November 22, 2019, 8:10am
12
Dear @samir ,
for Static Date what format/variable type i have to input to change #08 /01/2019# ?
thankyou
samir
(Samir Mohite)
November 22, 2019, 9:12am
13
@aliaga
If you’re using variable to give input as date then you have to use String var of with MM/dd/yyyy format. As shown in below shot,
dt.Select("[Letter Date] >= '#"+date1+"#' AND [Letter Date] <= '#"+date2+"#'").CopyToDataTable
e.g
2 Likes
aliaga
(Ali Aga Mustofa)
November 22, 2019, 9:50am
14
Hello @samir ,
Sorry for asking again, i got an error :
samir
(Samir Mohite)
November 22, 2019, 10:09am
15
aliaga:
Sorry for asking again,
No worries,
Your Start Date and End Date should be in MM/dd/yyyy format. so change that End Date —> 11/21/2019 (instead of 21/11/2019) @aliaga
system
(system)
Closed
November 25, 2019, 10:09am
16
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.