topic Mapping multiple fields in QlikView

Mapping multiple fields

Tue, 03 May 2016 22:34:47 GMT

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

Re: Mapping multiple fields

swuehl — Tue, 03 May 2016 22:39:29 GMT

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

Re: Mapping multiple fields

swuehl — Tue, 03 May 2016 22:42:08 GMT

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

Re: Mapping multiple fields

Tue, 03 May 2016 22:57:32 GMT

Thanks Stefan great answer.

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

Re: Mapping multiple fields

swuehl — Tue, 03 May 2016 23:02:13 GMT

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.

Re: Mapping multiple fields

Tue, 03 May 2016 23:45:38 GMT

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

Re: Mapping multiple fields

sunny_talwar — Tue, 03 May 2016 23:58:36 GMT

What is the exact are you getting?

Re: Mapping multiple fields

Wed, 04 May 2016 10:54:40 GMT

Got it working in the end - thanks stefan

Re: Mapping multiple fields

Wed, 04 May 2016 11:48:02 GMT

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

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

THEN

apply original mapping statement

What would be the correct parentheses for this ?

Thanks

Re: Mapping multiple fields

swuehl — Wed, 04 May 2016 11:55:08 GMT

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