Apply Map for Multiples Fields

[vikasmahajan]

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

[jonathandienst]

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,

     ...

[priyarane]

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;

[petter · Accepted solution]

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

[ashfaq_haseeb]

Hi,

You have to use 3 mapping load separately.

Regards

ASHFAQ

[vikasmahajan]

where are you after long time ?

[vikasmahajan]

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

[SreeniJD]

Hi Vikas

Did your issue solved?

Sreeni

[vincent_ardiet]

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

[vincent_ardiet]

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 ;