if-then-else vs mapping vs wildmatch

[Pico]

Hi all,

Right now I would like to find the better way to deal with the following problem and I need some advices.

I have more than 200 distinct IDs, without a fixed structure, they are usually like: 'qwer-it-cost-003-poiuy', 'asdf-004-de-mark', 'fr-seba-atie-004-qwer'.

One thing I know for sure (and the one I'm looking for) is that they contain nationalities, like 'it', 'de', 'fr', somewhere. In total there are 25 nationalities.

At this point I would like to map those nationalities, so if they contain 'de' I want 'Germany' as output, 'it' -> 'Italy' etc.

What is the best tool to deal with it? 

The applymap function? but I have a LOT of different IDs...

The if-then-elseif-else structure? Maybe combined with wildmatch? And combined how?

Final note, I am loading these IDs from SQL, but I can load first, ad then manipulate them. 

Thank you very much for your help, is much appreciated.

Have a nice day

[stevedark]

Hi @Pico 

I would probably look at a large nested if statement, with index statements in it, so:

if(index(Upper(ID), 'DE', 'Germany',
if(index(Upper(ID), 'FR', 'France,
if(index(Upper(ID), 'IT', 'Italy',
etc.))))))))))))))))))))))))))) as Country,

The problem with this is I expect that you will get false positives on the matching.

If the country always appears within hyphens, or at the beginning or the end you could adapt to this:

if(index('-' & Upper(ID) & '-', '-DE'-, 'Germany',
if(index('-' & Upper(ID) & '-', '-FR-', 'France,
if(index('-' & Upper(ID) & '-', '-IT-', 'Italy',
etc.))))))))))))))))))))))))))) as Country,

None of it is particularly pretty, but then unstructured data never is.

Hope that points you in the right direction.

Cheers,

Steve

[Pico]

Thank you for your idea.  The false-positive issue actually is not a problem because I have simplified the fields (I do not have 'de', I have 'de0035' and so on) hence I'm safe, but thanks for the smart input.

The solution I'm using right now is the following:

load  if(Wildmatch (Proj_ID, '*it*'), 'Italy',

if(Wildmatch (Proj_ID, '*fr*'), 'France',

if(Wildmatch (Proj_ID, '*de*'), 'Germany',

...

, 'Other Countries' ) ...))) as LOCATION,

*;

It does work, but it takes ages to load. Is there a chance to make it faster? Or is there another way?

[rwunderlich]

Here is a technique to "map" data using wildcards:

Qlikview Cookbook: Mapping With Wildcards
https://qlikviewcookbook.com/recipes/download-info/mapping-with-wildcards/

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com