Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Conditional renaming of certain values on import

Hi folks,

I've got 'Customer' data I'm importing from SQL database, and one of the fields is 'County' which is basically just a sub-national classification. Because of inconsistent importing of the data, we have multiple values which mean the same thing, for example, 'Virginia' and 'VA'.

I can change one of these mismatches by using the following code in my LOAD statement:

if(County = 'Virginia', 'VA', County) as County;

but I'm stumbling on how to get more than one mismatched value, such as

if(County = 'Utah', 'UT', County) as County,

if(County = 'Virginia', 'VA', County) as County;

I know I'm going about this the wrong way, and I was hoping someone out there might know how I could do this for multiple cases. Thanks!

Jonathan

6 Replies
balrajahlawat
Esteemed Contributor

Re: Conditional renaming of certain values on import

you have to do in this way like:

if(County = 'Utah', 'UT',

if(County = 'Virginia', 'VA',

if(County = 'XCV', 'XC',County))) as County;

balrajahlawat
Esteemed Contributor

Re: Conditional renaming of certain values on import

No need to add Country Field again and again, once you are done with mismatch then in else part at the end of condition you need to mention Country Field

simondachstr
Valued Contributor III

Re: Conditional renaming of certain values on import

This example is bad practise balrajahlawat - you should be maintaining a mapping table in Excel or Inline ([From Field], [To Field]) and use the ApplyMap() functionality in your load statement.

Re: Conditional renaming of certain values on import

Hi Jonathan,

Try pick and match function like below,

CountyData:

LOAD *,

Pick(Match(County,'Utah','Virginia','XCV')+1,County,'UT','VA','XC') as CountyNew;

LOAD * INLINE [

    County

    Utah

    XCV

    sd

    Virginia

];

Let me know.

MVP
MVP

Re: Conditional renaming of certain values on import

I agree with mahlerma - do not hard code a lookup like this. Use a mapping table that can be loaded from an inline load or (better) an external source like Excel or a database table. Then when new or changed mappings are needed, there is no need to dive into the code. Especially for a case like this when you might pick up new abbreviations from time to time.

As an example for inline (which is easily converted to an external source if required):

MapLookups:

Mapping LOAD * Inline

[

  From, To

  Utah, UT

  Virginia, VA

  Vgna, VA

  Virg, VA

  ...

];

Data:

LOAD

  ...

  Applymap('MapLookups', County, County) As County

  ...

FROM ...;

This will return the original value if the value is not in the lookup table.

reddys310
Honored Contributor II

Re: Conditional renaming of certain values on import

Hi jonathan,

Applymap() is the way to go. You can even use LOOKUP() to achieve the same.