topic Re: Apply Map for Multiples Fields in QlikView

Apply Map for Multiples Fields

vikasmahajan — Thu, 15 Oct 2015 06:05:35 GMT

Dear All

I have following data Table A

Code Sales

1001 5000

2033 6000

3000 7000

1002 8000

and Table B

Code , Add1,Add2,Add3

1001 , PPP,QQQ,RRR

2033 , XXX,YYY,ZZZ

3000 , JJJ,KKK,LLL

I want to use apply map between this tables , as per qlikview takes only one parameter with apply map , is there any way take directly 3 fields of table B into Table A with joins if any approach in apply map directly.

Thanks in Adv

Vikas

Re: Apply Map for Multiples Fields

jonathandienst — Thu, 15 Oct 2015 06:08:53 GMT

Create a bunch of mapping tables from table B:

Map1:

Mapping LOAD Code, Add1 resident TableB;

Map2:

Mapping LOAD Code, Add2 resident TableB;

...

Then use these as normal in ApplyMap statements.

TableA:

LOAD Code,

Sales,

ApplyMap('Map1', Code) As Add1,

ApplyMap('Map2', Code) As Add2,

...

Re: Apply Map for Multiples Fields

priyarane — Thu, 15 Oct 2015 06:16:31 GMT

Hi Vikas,

Only Jonathan suggestion is possible for applymap and also you can do with resident load like load code, add1 resident tab;

load code, add2 resident tab;

Re: Apply Map for Multiples Fields

petter — Thu, 15 Oct 2015 06:28:59 GMT

You could create a mapping table that contains multiple fields in on field and then split them out after having done the applymap by using the SubField()-function:

MAP:

MAPPING LOAD

Code,

Add1 & Chr(9) & Add2 & Chr(9) & Add3 AS FieldValues

FROM

....;

LOAD

Code,

SubField( 'MAP' , Chr(9) , 1 ) AS Add1,

SubField( 'MAP' , Chr(9) , 2 ) AS Add2

SubField( 'MAP' , Chr(9) , 3 ) AS Add3

RESIDENT

TABLE_A;

The only advantage over Jonathan's suggestion is that you only have to have one mapping table. The disadvantage is that it will store it's values less efficiently since compression will be lower and possible slower by using SubField ...

Re: Apply Map for Multiples Fields

ashfaq_haseeb — Thu, 15 Oct 2015 06:36:06 GMT

Hi,

You have to use 3 mapping load separately.

Regards

ASHFAQ

Re: Apply Map for Multiples Fields

vikasmahajan — Thu, 15 Oct 2015 06:37:52 GMT

where are you after long time ?

Re: Apply Map for Multiples Fields

vikasmahajan — Thu, 15 Oct 2015 06:39:21 GMT

Thanks for all reply I don't want 3 load instead of that can I do this in one shot !!

Hope there must be some thing !!

Vikas

Re: Apply Map for Multiples Fields

SreeniJD — Thu, 15 Oct 2015 06:40:44 GMT

Hi Vikas

Did your issue solved?

Sreeni

Re: Apply Map for Multiples Fields

vincent_ardiet — Thu, 15 Oct 2015 06:41:22 GMT

Hi Vikas,

What do you want exactly to map with what?

For my understanding you want to retrieve the 3 columns of TableB into TableA.

Like this:

Code Sales Add1 Add2 Add3

1001 5000 PPP QQQ RRR

Why aren't you just joining the tables?

Or do you want to obtain this?

Code Sales Add

1001 5000 PPP

1001 5000 QQQ

1001 5000 RRR

Regards,

Vincent

Re: Apply Map for Multiples Fields

vincent_ardiet — Thu, 15 Oct 2015 06:45:27 GMT

If it's the second one, in one load maybe like this:

left join (tableA)

load

Code,

Subfield(Add1&'|'&Add2&'|'&Add3,'|') as Add

resident tableB ;

Re: Apply Map for Multiples Fields

vikasmahajan — Thu, 15 Oct 2015 06:49:08 GMT

No...

Re: Apply Map for Multiples Fields

vikasmahajan — Thu, 15 Oct 2015 06:49:57 GMT

Thanks

I want to use apply map not join.

Vikas

Re: Apply Map for Multiples Fields

rubenmarin — Thu, 15 Oct 2015 06:59:46 GMT

Hi Vikas, check the response of petter-s above, it's in one shot using ApplyMap()

Re: Apply Map for Multiples Fields

vikasmahajan — Thu, 15 Oct 2015 07:09:35 GMT

Thanks I will check and get back you soon..

Vikas