Qlik Community

Qlik Education Discussions

Discussion Board for collaboration related to Qlik Education.

Not applicable

Field creation while data modelling

Hello,

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.

Attached is the sample QVW.

Regards,

Imran K

2 Replies

Re: Field creation while data modelling

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.

But a better way is to use a mapping-table and applymap() - have a look here: http://community.qlik.com/blogs/qlikviewdesignblog/2012/09/18/one-favorite-function-applymap

- Marcus

MVP
MVP

Re: Field creation while data modelling

Hi

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).

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Community Browser