How to get column from one table to another table with matched data?

[#1]

Hi,

I have two excel sheets.

Excel A have columns

code,
customer,
auditnumber,
customercommision

Excel B have columns
code,
customer,
customercommision

the values in excel A customercommision has below

4545797
462348476
383646
47566
37547
854747

values in excel A customercommision has below
4545797
462348476
45345345
383646
47566
37547
854747
363563565
565363565
4534534

How can I get auditnumber into excel B where customercommision values equals to excel A customercommision

I tried matching customercommision by using join in qvw but not getting correct result.

Please can anyone suggest me.

Thanks.

[rubenmarin]

ExcelA:

LOAD code,
customer,
auditnumber,
customercommision

....

ExcelB:

NoConcatenate LOAD code,

customer,

customercommision

.....

Left Join Load customercommision, auditnumber resident ExcelA;

DROP Table ExcelA;

Joining only by customercommision can link some unwanted rows if differents customers have the same commision, maybe you want to use a field that really identifies the customer.

[alexandros17]

If only customercommision link the 2 tables then:

Load * resident Table B .....

left join

Load

auditnumber,

customercommision

resident tableA

let me know

[veidlburkhard]

Hi Amelia,

in your script do like this:

AuditMap:

Mapping Load

customercommision,

auditnumber

From Excel A;

Load

code,

customer,

customercommision,

ApplyMap('AuditMap', customercommision, '-') as auditnumber

From Excel B;

Then you'll find the audtnumbers from A in B...

Hope this helps

Burkhard

[#5]

Thanks. Yes you are right joining the customercommision is not getting correct results. Please can you suggest me how to get this by joining customer.I have been struggling with this

[#6]

Thanks. I tried it is showing blank auditnumber. am I need to change anything in script. please suggest.

[veidlburkhard]

Show me your script, please.

[rubenmarin]

Hi Amelia, maybe:

ExcelA:

LOAD code,
customer,
auditnumber,
customercommision

....

ExcelB:

NoConcatenate LOAD code,

customer,

customercommision

.....

Left Join Load code, customer, auditnumber resident ExcelA;

DROP Table ExcelA;