Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Lookup, ApplyMap, Joins - Need Help

Table A
Ticket NoCustomer Name
123Shankar
121Arun
31231Vijay
34535Sandy
3534James

  

Table B
CustomerCity
ShankarChennai
ArunMadurai
VijaySingapore
SandyBelgium
JamesUSA

I want to map the city in Table B to the Customer Names in Table A.

1. I have tried renaming the Customer in Table B as Customer Name to create a sync but the result is not accurate.

Is there a way to create a lookup or applymap or any better solution? Please suggest.

Tags (1)
1 Solution

Accepted Solutions
Highlighted

Re: Lookup, ApplyMap, Joins - Need Help

Try this, You just had the word MAPPING missing from your load of Table B, and you must load the mapping table BEFORE you use the applymap command.

TableB:

MAPPING LOAD

        Customer,

         City

FROM

(ooxml, embedded labels, table is Sheet1);

TableA:

LOAD [Ticket No],

    [Customer Name],

    ApplyMap('TableB',[Customer Name],'Not Specified') as City

FROM

(ooxml, embedded labels, table is Sheet1);

View solution in original post

35 Replies
Highlighted
Specialist II
Specialist II

Re: Lookup, ApplyMap, Joins - Need Help

yes you can load

a mapping table with

AppAdd:

Customer Name CityS

hankar Chennai

Arun Madurai

Vijay Singapore

Sandy BelgiumJ

ames USA

the do a aplymap in table one 
Applymap('AppAdd',[Customer Name]) as CustCity
or you can have a left join with table A
Load Table A
;
Left Join (A)
Load Tale B ;
Thanks
BKC
Highlighted
Not applicable

Re: Lookup, ApplyMap, Joins - Need Help

Hi,

Try this :

TableB:

Mapping load

               Customer as [Customer Name],

               City;

// SQL SELECT * From Table B; //

TableA:

load

               [Ticket No],

               [Customer Name],

               APPLYMAP('TableB',City,null) as City;

// SQL SELECT * From Table A; //

Highlighted
MVP
MVP

Re: Lookup, ApplyMap, Joins - Need Help

[Table A]:

load * inline [

Ticket No, Customer Name

123, Shankar

121, Arun

31231, Vijay

34535, Sandy

3534, James

];

[Table B]:

load Customer as [Customer Name], City

inline [ 

Customer, City

Shankar, Chennai

Arun, Madurai

Vijay, Singapore

Sandy, Belgium

James, USA

];

Highlighted

Re: Lookup, ApplyMap, Joins - Need Help

Use a mapping table like this. You will need to use your correct field names and table names.

The mapping table is deleted at the end of the load process so you will just have the Data table containing 3 fields.

City_Map:

mapping load

     Customer, City

from TableB ;

Data:

load

     TicketNo,

     CustomerName,

     applymap('City_Map', CustomerName, 'Not Specified') as City

from TableA

Highlighted
Not applicable

Re: Lookup, ApplyMap, Joins - Need Help

Hi,

Please see the attachment..Hope this will help you..

Highlighted
Creator
Creator

Re: Lookup, ApplyMap, Joins - Need Help

@ Colin,

applymap('City_Map', CustomerName, 'Not Specified') as City


We are mapping CustomerName from Data to City_Map table but to which column? Why we are specifying 'Not Specified'?

Highlighted
Creator
Creator

Re: Lookup, ApplyMap, Joins - Need Help

Hi Massimo,

I tried this and getting a result like below...

Ticket No,            Customer Name      City

123,                    Shankar                    Chennai

121,                    Arun                        Madurai

31231,                Vijay                        Belgium

34535,                Sandy                        Singapore

3534,                  James                        USA

123,                    Shankar                   

121,                    Arun                       

31231,                Vijay                       

34535,                Sandy                       

3534,                  James                       



I am getting one entry with City names joined and another entry with same ticket no and Customer Name without city. Please help.

Highlighted

Re: Lookup, ApplyMap, Joins - Need Help

Apply map does not use the field names of the mapping table, the columns can have any name.

It compares the specified field to the first matching value in column1 of the mapping table, and returns the value in column2, of no match is found, then the 3rd value of the applymap statement is executed.

In my example if there is no match, the text 'Not Specified' is returned

Highlighted
Creator
Creator

Re: Lookup, ApplyMap, Joins - Need Help

Thanks Colin. I am getting blank for all rows.. I am getting -