Lookup, ApplyMap, Joins - Need Help

[Anonymous]

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.

[Anonymous]

I have tried but getting blank.

[Anonymous]

Below script will give you correct result. Just try and let me know your result

TableA:

LOAD [Ticket No],

     [Customer Name] as Customer

FROM

(ooxml, embedded labels, table is Sheet1);

left join (TableA)

TableB:

LOAD Customer,

     City

FROM

(ooxml, embedded labels, table is Sheet1);

[Anonymous]

Hi Friends,

The actual problem is attached.

When I joined the 2 tables I am getting the City, Count of Tickets and the same ticket number is appearing without city.

So the count is getting doubled in my result.

Please see attached.

[Anonymous]

Hi,

The actual problem is attached.

When I joined the 2 tables I am getting the City, Count of Tickets and the same ticket number is appearing without city.

So the count is getting doubled in my result.

Please see attached.

[Anonymous]

have you tried left join ??

[Anonymous]

also can you attached your QVW

[MayilVahanan]

Hi Vijay,

Try like this

//Solution 1

Directory;

LOAD [Ticket No],

     [Customer Name]

FROM

[Table A.xlsx]

(ooxml, embedded labels, table is Sheet1);

Left Join

LOAD Customer as [Customer Name],

     City

FROM

[Table B.xlsx]

(ooxml, embedded labels, table is Sheet1);

//Solution 2

Map:

Mapping

LOAD Customer,

     City

FROM

[Table B.xlsx]

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

LOAD [Ticket No] as TicketNo,

     [Customer Name] As CustomerName,

     ApplyMap('Map',[Customer Name], 'NA') AS CityMap

FROM

[Table A.xlsx]

(ooxml, embedded labels, table is Sheet1);

[Anonymous]

The solution worked just by renaming Customer Name and without joins.

But This is just a sample data.

My real data is I am having DB with IT tickets and customer name. I am querying AD to get the user list with their city names. Then when map both table (Ticket Table and AD table) by renaming like customers as you advised. It is working fine. But for some users I dont have city name filled out in AD. So I getting a data like attached. For Example: St Neots I have 2 tickets. Bit the same ticket number is coming up also in the list of blank city names. When I tried this using a flat file excel (the same amount of data (1000 tickets) I have no issues. But when joining as tables I am getting the below issues. I dont know why IN98301 is coming in St Neots as well as in the blank City tickets.

I am breaking my head

[Colin-Albert]

This will not work for two reasons:

Firstly the mapping table must be loaded before it is used in an applymap statement.

Secondly if you are using an apply map to bring the relecant City into table A based on the Customer Name, then you should not use a left join as well.

[Colin-Albert]

Hi Vijay,

Have a look at this blog which explains the advantages of using ApplyMap instead of Joins.

Don't join - use Applymap instead