Hi,
I have an excel table with 3 filter options. First 2 work and 3rd one is throwing me errors. below are the details: someone please help resolving the issue.
I have been trying different ways and not successful. Please help.
Thanks,
V
Hello, that error means that itâs not detecting the column. Check your read range activity or check that you donât have any spaces in the excel file. The name must be the same.
1 Like
You have couple of problems here
As I have highlighted, it looks like you are trying to get records less than todayâs date in the 3rd condition. I think you can change it like this to see whether it works.
Hi @rmunro,
All my column headings have spaces. So I have enclosed it in brackets like (âIssue Color Statusâ). Is this the correct way? If you have a sample of data filter work flow could you please share? I am not able to get over the error.
Thanks,
V
Fine
to filter in such cases which has date involved
then use a select method rather to Filter datatable activity
yourdatatablename = yourdatatablename.Select("[Issue Color Status] like â%Green%â or [Issue Color Status] like â%Yellow%â and [Issue Original Target] < # " + Now.ToString(âyourdateformatâ) + " #).CopyToDatatable()
where yourdateformat is based on what you have in the excel if its like dd/MM/yyyy then mention the same here in the select method, or mention based on the format in excel
and for this actually while using in filter datatable activity we dont need to mention the curly brackets, only the name of the column within double quotes like this
âIssue Color Statusâ
hope this would help you
kindly try this and let know for any queries or clarification
Cheers @Vidya_Lingappa
Hi @Palaniyappan, I tried just now and getting the error:
did select method work buddy
Cheers @Vidya_Lingappa
Hi Lahiru.Fernando, Could you please share an example workflow if you have?
Hi @Palaniyappan, Are you suggesting not to use the data filter method and use the select method? Where do I give the select statement? Please let me know.
Thanks,
V
we can mention this in assign activity
yourdatatablename = yourdatatablename.Select("[Issue Color Status] like â%Green%â or [Issue Color Status] like â%Yellow%â and [Issue Original Target] < # " + Now.ToString(âyourdateformatâ) + " #).CopyToDatatable()
Cheers @Vidya_Lingappa
Hi @Planaiyappan, Could you please share with me a workflow?
Kindly share a sample excel file so that we could come up with a excel
Cheers @Vidya_Lingappa
*xaml
Please check the attachedâŚ
DataTableFilter.xaml (6.0 KB) TestData.xlsx (8.3 KB)
Thanks so much for sharing the workflow @Lahiru.Fernando . My excel table columns headings have spaces like âEmployee Nameâ, âJoin Dateâ. What is the best way to handle? Should the columns be just within double quotes or double quoted within brackets like (âEmployee Nameâ). Please let me know.
I checked the same code with spaces in column headers. Adding space like âEmployee Nameâ for the column name in the filter data table activity works fine. You can also use it within brackets as well. Doesnât make much of an difference. However, if your column names contain more special characters, I would suggest to have them within brackets just to be on the safe sideâŚ
In your scenario, having space would not be that much of an issue.
Hi @Lahiru.Fernando, I just ran my script and it ran, but nothing got filtered. the columns are of type âGeneralâ. Is this anything to do with?
I added a build data table activity and created a datatable with a string and date time column for my excel sheet. Then tested it against the spaces in the column names. But it worked perfectlyâŚ
Whatâs the error that you are getting?
It ran, but nothing got copied to the result sheet. I have no idea what is happening.
Do you have data that falls within the criteria of your filter?
Additional point: Test this using debug mode.
Right click on the filter data table activity and add a break point
Then run the program in the debug mode
Once the execution gets to the filter data table activity, it will highlight the activity in yellow.
Then click on Step Into to execute the highlighted step.
Once you finish the execution of the filter data table activity, under the Locale panel, see what you have for your data table variable. It should show what it holds.
Can you share a screenshot of that?