Hi All ,
i have two data tables, one is “Input Dump” (which has 29 columns)
another is "Criteria’ (which has 7 columns)
now , for every row in Criteria (ie depends on the value of the columns in that row) , there will be a set of rows that can be fetched from input dump.
lets assume there are 5 rows in criteria.
so the result of this filter is 5 data tables ,
and now i need all the 5 data tables merged together as one . most importantly out of the seven columns in the criteria table , some may be empty also. so i cannot be the criteria we can ignore that.
how to achieve this in uipath ? @Palaniyappan @loginerror any idea?
1 Like
You can use Build Data Table to create a structure and then run your for loop, check conditions and in case of a match, use an Add Data Row to add the value to your data table.
By default, you’re adding data to the same table and once a loop is complete, write it somewhere and clear your data table.
2 Likes
i am not getting your logic.
[quote=“monsieurrahul, post:2, topic:218129”]
You can use Build Data Table to create a structure and then run your for loop
[/quote] create structure of the resultant table ?
not getting this at all ! and my main problem is how do you filter data in one table based on criteria in another table ? especially there is a possibility that the criteria table might have empty string values in it. your answer is not dealing with this part AFAIU
1 Like
@Seetharaman_K Can you explain the Process what you want to do using Sample Input Data and Expected Output Files, It will be easier to understand the logic 
1 Like
CriteriaSheet.xlsx (8.9 KB) InputDump.xlsx (2.6 MB)
uploading a sample of two sheets.
all the criteria in the sheet should be applied on the dump.
eg : second row of criteria sheet is as follows
so on the input dump i need all rows which are having
CLIENT as A59A1948 and MARQUE = “MICHELIN” and CODE ARTICLE = “606056” .
this will return a set of rows ,
so for each row in critera we have a set of row in input dump.
i want to extract all those rows and combine that as one data table
1 Like
@Seetharaman_K The Column names in the Input Dump is not in English, so having a bit Difficult in Mapping Columns, Can you map the Column Names in Criteria with the InputDump, so that we can map the column names properly and get the Rows needed
2 Likes
we can ignore “Leader condition” and “CSC”. and DPL is a date so there will be one more column in the criteria sheet which will say
records need to be pulled are less than this date
or grater than this date
or equal to exact date
1 Like
@Seetharaman_K In the Critiria Sheet, is there a Column Which always contains data? There are some Columns with Data and Some without, So Can’t really make it dynamic if there is No data that is present always.
2 Likes
excatly as i said . it can be empty as well. that means there is no filter text for that crtieria we can simply ignore that criteria , makes sens ?
1 Like
@Seetharaman_K Yes, But it might be a bit difficult to get the results needed . Wait I’ll tag guys who are more well versed with these sort of things, they might be able to help you in a Better way.
@ppr
2 Likes
@supermanPunch thanks for referencing and coordination
@Seetharaman_K
Please help me on freezing the requirements and confirm my understanding:
- with Customer Account we do find corresponding rows in input when it is matching client
- in case of we more filter criteria then we have to apply additional and dynamic on its occurence
for this I do need your help as well:
- please list down the column names that you are expecting in the output table
- provide some examples for the DPL date case
Thanks for feedback
2 Likes
I defined a mapping table for constructing the search expression parts
Within a For each row over dtCriteria following LINQ generated the Search String respecting the dynamics
(From m In dtMap.AsEnumerable
Let val = row(m(0).toString).ToString
Where Not String.IsNullOrWhiteSpace(val)
Select para = m(1).toString.Trim.Replace("<par>", val )).toArray
- iteration over dtMap
- memorize the value of dtCriteria iteration row from Column with ColName as by current m(0) // Mapping table
- let it pass when value is present
- us the stringtemplate from mapping and replace place holder with the value from dtCriteria
after some minor things the seach strings were used in dtInput.Select method the filtering and did the the filtering
Find starter help here
Seetharaman_K.xaml (16.8 KB)
Kindly note: this was a fast prototype and you have to complete it e.g for this date.
Let us know in case of you need further help
2 Likes
HI @ppr you solution i guess it worked (still verifying with more test inputs ) . before i close i wana ask couple of questions
(From r In drSearchResults
Select dtResult.Rows.Add(r.ItemArray)).CopyToDataTable → Why we use dtresult but not direct dtinput ?
secondly , why item array can u please explain this statement ?
thirdly , i need column names in the resultant table , while am usinge"AppendRange"every time using the “tmpResult” to a resultant excel sheet , i get the necessary filtered data but am loosing the column names of those data !
finally ,
i cannot understand the tag
all above , am trying to find answerws , but posted here as an update.
and coming back to your answer, imust really thank you . i will close this thread in couple of days after seeing the result.
Why we use dtresult but not direct dtinput ?
It is faster to implement of adding the relevant to a new empty datatable
why item array can u please explain this statement
the item array is an array of objects representing the column values of a row. Wth this statement the content is added / transfered to the other datatable and detached from origin link to inital datatable (Links, by value by data concept, datarow.datatable association…)
i need column names in the resultant table
You do have. first checkout the used technique: filtered rows content is added to dtResult DataTable. The Part result is returned to dtTemp. this helps for debugging or maintenace analysing to t inspect the part result.
All filtered rows are added to dtResult. Writing back to Excel with add headers also will take care about the column names
i cannot understand the tag
I did not understood your question, but also have a look on my elaboration from above.
Dynamics are to handle as it is unknown if a filter criteria is to use or not. With the mapping table the search string part is configured and will be applied, if dtInput value is not empty. From the search part template the placeholder <par> (was a trypo and sjpuld have the name para) will be replaced by actual column value. After all the entire search string will be constructed with the string.Join statement of the filter criteria that needs to be applied
1 Like
this is not happening
this is happening for sure ,
i think instead of dttemp i will have to keep on appending one datatable . in which case i will have the cosolidated filtered result but i will loose the column header. i need to see an laternate way to include the header.
i will have a deeper look on it this evening. But just one thing to clear: You are interessted on a excel holding all filter results, right?
right i just checked. test criteria 1.xlsx (1.2 MB) for this excel file , the criteria is returning hardly few rows i think its 6 or 7. and 127 columns.
i need only 29 columns, this i can remove from the consolidated result also .
but for everything column name is needed.
in one word . Yes.
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.