I am having a trouble in getting correct data. I am having a Product name which consist of Coutry code and i want to categorised Product name with Country code. I am creating Coutry code field from Product Name field.
After creating Coutry code field, i am having an issue. For example, If the Product name is NL SAP HANA, and i have created a Coutry code field from "NL SAP HANA" and after reload i am getting country name as NA rather than NL. I am having huge data to create Courty code field. Also country code can anywhere in the product name.
Generally it could cause matching-errors if you matched like If(... = 'NA', ... but in your case it seems that the country code is separated by a space, so you could use If(... = 'NA ', ... or If(... = ' NA', ... whereas I would rather use a pick(wildmatch()) function instead a nested if-loop.
See the attached. I tokenise the product names using subfield() and then do an applymap to match the tokens to the country codes, using ZZ as a default for no match. Then I get the min string value for the serial no (or Other if it is ZZ).
This will work reasonably well if you have a small set of countries. If you have many (eg the full ISO 3166 list) then the error rate will go up, as almost any 2 char token will match something (eg NA matches Namibia; BI matches Burundi).
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein