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.

[maxgro]

pplease ost your doc

my result, with the data you posted, is this

[Anonymous]

Have you tried Left join ?

[Anonymous]

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.

[Colin-Albert]

Can you post your script?

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

[Anonymous]

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

[Anonymous]

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

[MayilVahanan]

HI

PFA

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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