I have below excel data -
I have 2 values -
“currency” and “account”
First I have to check if [first 2 characters of " currency" ] is present in Remit_ID rows.If it has a unique row match , it should return the corresponding “Supplier_ID” for that Remit_ID. If its present in multiple rows or if no rows match, it should check for the [last 4 digits of " account"] in Remit_ID rows.If there is a unique match it should return the corresponding “Supplier_ID” for that Remit_ID.
For example -
currency - USD
account - 23421134
it should return - SUPPLIER_CONNECTION-3-3628
currency - BRA
account - 23423456
it should return - SUPPLIER_CONNECTION-3-2345
currency - BRA
account - 234234561
it should return - “No record found”
currency - USD
account - 7891011
it should return - SUPPLIER_CONNECTION-3-7654
For multiple matches even after currency and account validation , it should return any of the
Kindly help here!
I have flagged the original post and asked the moderator to delete it since the requirement is not precise and I havent achieved the desired solution on that.The requirements for this is different from the original one.
Have a look on the XAML file
ExcelProcessing.xaml (10.6 KB)
@gokul1904 I have got a chance to work on your process, but I was little bit confuse. Now you gave more examples, now I will be able to do something
Thanks a lot for the prompt response. but this list is a dynamic one where switchcase cannot be used
“currency” and “account” are inputs to this excel datatable
it should perform a match with first 2 characters of currency and last 4 digits of account number in the order as mentioned in the main post.
@gokul1904 so input is Coming outside and just we need to match with excel file.
Yes Input is coming from outside - currency and account are input variables here.
We have to match the input variables with each row of “Remit-ID” column and derive the corresponding “Supplier_ID” for that.
have a check at:
(From d In dtData.AsEnumerable
Let sc = strCUR.Trim.Substring(0,2).toUpper
Let sa = strACC.Trim.Substring(strACC.Trim.Length - 4).toUpper
Let rm = d("Remit_ID").toString.Trim.toUpper
Where rm.StartsWith(sc) Or rm.EndsWith(sa)
Let cm2 = Convert.ToInt32(rm.StartsWith(sc)).toString
Let cm1 = Convert.ToInt32( rm.EndsWith(sa)).toString
Let msk = CInt(cm1 & cm2)
Where msk > 0
Order By msk Descending
Select v = d("Supplier_ID").toString).DefaultIfEmpty("No record found").First()
Thank you @ppr for your response.However it is not giving desired output for 1st and 4th scenario -
I will elaborate the first example → [first 2 characters of “Currency” i.e USD] has multiple matches in rows 1,2,4.So it should validate with [last 4 digits of " account"i.e 1134].It has only one match in row 4.Hence it should give SUPPLIER_CONNECTION-3-3628 as output.
If [first 2 characters of “Currency” i.e USD] had a unique match , it should have given respective Supplier_ID and it wont validate for [last 4 digits of " account"i.e 1134]
Only when currency validation results in multiple matches it should proceed with the account validation to filter the multiple matches.
can you check updated statement please?
Thanks alot @ppr again!
But for values -
strCUR = EUR
strACC = 123456
it should return → SUPPLIER_CONNECTION-2-3627
whereas it is returning → SUPPLIER_CONNECTION-3-2345
Since strCUR i.e EUR is a unique match it should pick the corresponding Supplier_ID and not validate for strACC
Thank you once again for all the efforts.
Let us have a look at the rule definition
strCUR = EUR, strACC = 123456
EUR → has one Match so from your rule set it returns the 3627 Remit
3456 → has a match to the 2345 Remit
Currently the approach is assigning a rating mask base on cur / acc check
Where filtered out the cases of 0
We would suggest to advance with it, when you want take this approach and
- swap the most important criteria
- return all Remits with its rating mask and postprocess it
Thank you for the explanation @ppr
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.