I filter on thee seperated filters and write the values of each filter to it’s own sheet in a excel file, however the filters aren’t returning the values that I expected, anyone an idea of what’q happening?
The three filters in each it’s own wizard are:
“Status” = “Open”, “WIID” > 200000 and “Type” = “WI1”
Status is open should return everything, I haven’t counted the values but I asume it works
WIID > 200000 isn’t returning the same values as status is open, but stil has some rows where the WIID is smaller then the argument and WI1 is returning everything where the WIID is WI2, how is that even possible? the filters don’t have typo’s, I pass the values from the workflow that filters to the one that writes and checked the arguments, they’re all correct:
@Daniel_D I think the issue may be with your variables. Anything I have seen is that the “Name” for the arguments should be different than “Value”. Could be having the same name causes conflict. Not sure, but may be worth trying to rename if nothing else to rule it out.
So in your case
IN_dtFilterResultsType In DataTable dtFilterResultsType
IN_dtFilterResultsWID In DataTable dtFilterResultsWID
IN_dtFilterResultsStatus In DataTable dtFilterResultsStatus
Or it might be the out variables - not sure how your workflows are setup.I don’t pass variable much, so not a lot of experience, but, stuff I came across had different names.
Normally I do that automaticly, but now I kinda spaced out
On a different note, I tried to use a asign and select and then coppy the datarowarray in to a datatable, like:
out_FilteredResultsStatus = in_ExtractDataTable.Select(“Status = ‘Open’”).CopyToDataTable
that seems to work for the WIID > 200000 but still fails for the type.
I’ll try renaming, not cause I think it will work, but just to make sure
As expected the result is the same…
It’s weird that dataTable.select(“Type = ‘WI1’”) isn’t working, since I have use something simular numerious of times without any issues
Can you output your datatable to string or csv and upload here?
Note: .csv and .txt files aren’t allowed. Just change the extension to .html or another allowed type and tell us what extension we should change it back into
I guess either would work. If you’re using a read range from an excel file and not manipulating anything, then the excel file would work. If any manipulations have taken place, then you should output the datatable right before where you’d be doing the filtering.
I’m a little lost - are any of the files uploaded the datatable just before you try to filter it? I opened results, but I didn’t understand any of the 3 sheets in there. Would you mind uploading that datatable in addition to these? I don’t think I can run the file since i dont have credentials, etc setup
the datatable comes from the Datascraping, I scrape each page of the acme site, simular to the dispatcher of exercise 2 level 3, it’s output to process and in to filterData, which return three dataTables to process and back in to WriteToExcel
Can you add in a ‘write CSV’ activity that outputs your datatable immediately before trying to do the filtering? I have a feeling it is due to either the text values or column names not matching and would like to confirm.
If there would be a mismatch on the column wouldn’t I get an error? Column not found or something
And wouldn’t a mismatch on value simply not return anything?
Well if it’s not returning results as expected, that’s usually the cause. I am not familiar with the datatable filter activity though, as I usually use SELECT or lambda/LINQ in an assign to do any filtering. You tried a select filter that wasn’t working though, which leads me to believe there are characters grabbed in such as whitespace that need to be trimmed, or unexpected capitalization as select is matching case.
So when you extract that datatable shown in the picture, write it to a CSV. Manually check the file to make sure no spaces, etc are added in the CSV. Do this in notepad or another text editor NOT in excel. Since you are doing manipulation before filtering, do the same thing right before the filter. You just want to make sure the data you’re working with is exactly what you expect it to be. If everything appears to be working up to that point, try doing this select statement in an assign activity:
Create a variable/argument of type datatable i’ll call filteredDT Assign filteredDT = in_ExtractDataTable.Select("[Status] = 'Open' and [WIID] > 200000 and [Type] = 'WI1'").CopyToDataTable()
This should output all the datarows matching the criteria within the select statement. If it throws an error you know it could be due to bad headers or types like you had stated. If it is running fine, but not filtering properly, then there is a 99% likelihood that the input data is incorrect
You where right… Can’t find column Status…
Heres is the csv file of the whole datatable, as you can see the column is there…test.html (4.7 KB)
you’ll have to change it back from html to .csv though
Anil_G
Yoichi
Shivam_Rana
allbran
Shige2018
sasi_poosarla
Gaurav_Malhotra