Conditional renaming of certain values on import

[#1]

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

[Anonymous]

you have to do in this way like:

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

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

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

[Anonymous]

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]

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.

[tamilarasu]

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.

[jonathandienst]

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.

[reddy-s]

Hi jonathan,

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