Mapping multiple fields

[#1]

Hi I have a mapping file which has the following

Salesman, Division1, Division2, Division3

My data file contains the fields:

Salesman, Region1, Region2, Region3

I know how to do a standard mapping but feel this one may need some tweaking. What I want to do is map the Salesman field so that where ever there is a match on Salesman then the corresponding values for Divisions 1,2 and 3 replace the values in Regions1,2 and 3

[swuehl]

Maybe like

MAP:

MAPPING

LOAD Salesman, Division1 &'|'& Division2 &'|'& Division3

FROM ...;

LOAD Salesman,

          Subfield( ApplyMap('MAP', Salesman),'|',1) as Region1,

          Subfield( ApplyMap('MAP', Salesman),'|',2) as Region2,

          Subfield( ApplyMap('MAP', Salesman),'|',3) as Region3,

...

FROM ...;

[swuehl · Accepted solution]

and if you need to keep Region values for non matching Salesman, try

LOAD Salesman,

          Subfield( ApplyMap('MAP', Salesman, Region1 & '|'),'|',1) as Region1,

          Subfield( ApplyMap('MAP', Salesman, '|' & Region2),'|',2) as Region2,

          Subfield( ApplyMap('MAP', Salesman, '||' & Region3),'|',3) as Region3,

...

FROM ...;

edit:

Or just use three mapping tables, one for each Division and a simple ApplyMap() without the need for an additional Subfield().

[#4]

Thanks Stefan great answer.

Occurred to me after you could also just do a left join.

[swuehl]

Maybe.

If you want to replace Region fields with Division field values for matching Salesman but keep Region for non matching Salesman, using a JOIN alone wouldn't be enough, would it? I guess you would need to JOIN new fields to the first table, then decide which field values to use in a second Resident load.

[#6]

Yes you're right about the join. Going back to your code is this definitely correct as it seems to error for me :

>>

LOAD Salesman,

          Subfield( ApplyMap('MAP', Salesman, Region1 & '|'),'|',1) as Region1,

          Subfield( ApplyMap('MAP', Salesman, '|' & Region2),'|',2) as Region2,

          Subfield( ApplyMap('MAP', Salesman, '||' & Region3),'|',3) as Region3,

...

FROM ...;

[sunny_talwar]

What is the exact are you getting?

[#8]

Got it working in the end - thanks stefan

[#9]

To add a twist if I wanted to add an if statement around each of these mappings so that it would say ;

If (match (salesman, 'BOB')            // where salesman = BOB

OR

wildmatch(salesman, *%*)               // where salesman has character %

THEN

apply original mapping statement

What would be the correct parentheses for this ?

Thanks

[swuehl]

Maybe like

LOAD Salesman,

          If( Salesman = 'BOB' OR Index(Salesman, '%'), 

               Subfield( ApplyMap('MAP', Salesman, Region1 & '|'),'|',1)

               ,'Sorry, mapping should not be done because condition is not fulfilled')  as Region1,

          ...

...

FROM ...;