Hi All,
I have below excel columns for string comparison. I have used contains but not able to filter. because if string has comma, period is acceptable but using contains its going to else condition. Can any one help me on this.
Below is the sample data
| UR_ACCT_NAME | DNB_NAME |
|---|---|
| Timbercreek Equipment Co, LLC | Timbercreek Equipment Co LLC |
| Both column values are same one comma extra in first column still its going to else condition i need consider comma in string. |
Thnaks,
Hima
Hi @thima
Try this:
OutputDt= (From row In yourDataTable.AsEnumerable()
Where row.Field(Of String)("UR_ACCT_NAME").Replace(",", "").Replace(".", "").Trim() = row.Field(Of String)("DNB_NAME").Replace(",", "").Replace(".", "").Trim()
Select row).CopyToDataTable()
OutputDt is of DataType System.Data.DataTable.
Hope it helps
Hi
You can use this code in an assign activity with System.Data.Datatable data type variable
YourdataTablename.AsEnumerable.Where(Function(x) System.Text.RegularExpressions.Regex.Replace(x(“UR_ACCT_NAME”).ToString.trim, “[^a-z A-Z 0-9]”, “”).Equals(System.Text.RegularExpressions.Regex.Replace(x(“DNB_NAME”).ToString.trim, “[^a-z A-Z 0-9]”, “”)) ).CopyToDataTable
HI @thima
You can try this way in will work
System.Text.RegularExpressions.Regex.Replace(CurrentRow("UR_ACCT_NAME").ToString,"[,\s.]","").Contains(System.Text.RegularExpressions.Regex.Replace(CurrentRow("DNB_NAME").ToString,"[,\s.]",""))
Hi All,
I can able to filter data scenario is in first row both names are same but if i write a condition to compare due to comma its going to else condition. requirement is when name has comma or period we should consider.how to do that
Try to use condition like this
It should be working
CurrentRow(“UR_ACCT_NAME”).ToString.Trim.Replace(“,”, ””).Equals(CurrentRow(“DNB_NAME”).ToString.Trim.Replace(“,”, “”)
Cheers @thima
For suppose if your row has same names it will match also the your row has the comma then it will match the condition and it will not go to the else condtion .
Try this expression for the oputput : -
System.Text.RegularExpressions.Regex.Replace(CurrentRow("UR_ACCT_NAME").ToString,"[,\s.]","").Contains(System.Text.RegularExpressions.Regex.Replace(CurrentRow("DNB_NAME").ToString,"[,\s.]",""))
Note : - in if condition you can try the above expression it i will work
Note : - For the above Expression it will replace the comma and dot and even also the white spaces and compare the two rows then the both rows are equal then it matches ( You can perform the actions ) or else it go to the else condition.
try using this in an assign activity without for each row
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.
