Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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.

1 Solution

Accepted Solutions
Colin-Albert

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
Anonymous
Not applicable
Author

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
Not applicable
Author

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

maxgro
MVP
MVP

[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

];

Colin-Albert

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

Not applicable
Author

Hi,

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

Anonymous
Not applicable
Author

@ 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'?

Anonymous
Not applicable
Author

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.

Colin-Albert

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

Anonymous
Not applicable
Author

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