topic Re: Lookup, ApplyMap, Joins - Need Help in QlikView

Lookup, ApplyMap, Joins - Need Help

Anonymous — Wed, 27 May 2015 08:02:16 GMT

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

Table B
Customer	City
Shankar	Chennai
Arun	Madurai
Vijay	Singapore
Sandy	Belgium
James	USA

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.

Re: Lookup, ApplyMap, Joins - Need Help

Anonymous — Wed, 27 May 2015 08:08:15 GMT

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

Re: Lookup, ApplyMap, Joins - Need Help

Wed, 27 May 2015 08:12:31 GMT

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

Re: Lookup, ApplyMap, Joins - Need Help

maxgro — Wed, 27 May 2015 08:16:56 GMT

[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

];

Re: Lookup, ApplyMap, Joins - Need Help

Colin-Albert — Wed, 27 May 2015 08:22:14 GMT

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

Re: Lookup, ApplyMap, Joins - Need Help

Wed, 27 May 2015 08:45:41 GMT

Hi,

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

Re: Lookup, ApplyMap, Joins - Need Help

Anonymous — Wed, 27 May 2015 08:56:02 GMT

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

Re: Lookup, ApplyMap, Joins - Need Help

Anonymous — Wed, 27 May 2015 08:58:53 GMT

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.

Re: Lookup, ApplyMap, Joins - Need Help

Colin-Albert — Wed, 27 May 2015 09:00:20 GMT

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

Re: Lookup, ApplyMap, Joins - Need Help

Anonymous — Wed, 27 May 2015 09:05:38 GMT

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

Re: Lookup, ApplyMap, Joins - Need Help

maxgro — Wed, 27 May 2015 09:12:25 GMT

pplease ost your doc

my result, with the data you posted, is this

Re: Lookup, ApplyMap, Joins - Need Help

Anonymous — Wed, 27 May 2015 09:16:37 GMT

Have you tried Left join ?

Re: Lookup, ApplyMap, Joins - Need Help

Anonymous — Wed, 27 May 2015 09:21:52 GMT

Hi Chandel,

Below is the script I am using.

TableA:

LOAD [Ticket No],

[Customer Name],

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

FROM

(ooxml, embedded labels, table is Sheet1);

TableB:

LOAD Customer,

City

FROM

(ooxml, embedded labels, table is Sheet1);

Result is below.

I am not using inline data. I am using data from 2 sample excel files.

Re: Lookup, ApplyMap, Joins - Need Help

Colin-Albert — Wed, 27 May 2015 09:22:13 GMT

Can you post your script?

you will need to change my example script to match your actual field names, tables and file paths.

Re: Lookup, ApplyMap, Joins - Need Help

Anonymous — Wed, 27 May 2015 09:24:15 GMT

I have attached my files that I am using as data, the script and the result. please review and share your suggestions.

Re: Lookup, ApplyMap, Joins - Need Help

Anonymous — Wed, 27 May 2015 09:25:28 GMT

Hi Colin please review the script, files with data and the result posted above and share your thoughts.

Re: Lookup, ApplyMap, Joins - Need Help

MayilVahanan — Wed, 27 May 2015 09:31:23 GMT

PFA

Re: Lookup, ApplyMap, Joins - Need Help

Anonymous — Wed, 27 May 2015 09:34:01 GMT

I think this will resolve your problem. Try and let me know what data you are getting.

TableA:

LOAD [Ticket No],

[Customer Name] as Customer,

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

FROM

(ooxml, embedded labels, table is Sheet1);

left join (TableA)

TableB:

LOAD Customer,

City

FROM

(ooxml, embedded labels, table is Sheet1);

Thanks

BKC

Re: Lookup, ApplyMap, Joins - Need Help

Anonymous — Wed, 27 May 2015 09:39:17 GMT

See the attached with Left join

Ticket No	Customer	City
121	Arun	Madurai
123	Shankar	Chennai
3534	James	USA
31231	Vijay	Singapore
34535	Sandy	Belgium

Re: Lookup, ApplyMap, Joins - Need Help

Anonymous — Wed, 27 May 2015 09:41:34 GMT

I am using personal edition. hence I cannot open your QVW. Please suggest in the forum.