Inner Join table with 2 matching column condition

[#1]

Hi,

I have Table1 with columns VendorName , Amount, date and many other columns.

Table2 with columns VendorName , Amount, Category(with only one value reversal.

What is the best way to join both the tables in such a way that table1.VendorName= table2.vendorName AND table1.Amount=table2.Amount and the column category is added in table 1 (where the condition matches it has value "reversal " and where the condition doesnot match it says other)

Any suggestions on how to sort this kind of data.

Thanks in advance.

Regards,

S

[sunny_talwar]

May be this:

Table1:

LOAD VendorName,

          Amount,

          date,

          ...

FROM Source1;

Inner Join(Table1)

LOAD VendorName,

          Amount,

          Category,

          ...

FROM Source2;

Make sure no other field names match between the two tables, if they do, then rename that particular field in one of the two tables.

[Qrishna]

You can try Sunny's Solution but make sure you join your tables on Numerical keys instead of Alphanumeric(i guess VendorName field is non-numeric) else the join would eat up your RAM.

for that sake, you may want to try Autonumber(Field) function.

[oscar_ortiz · Accepted solution]

You may want to try using a mapping load and applymap, this will give you the option of supplying a default value if you do not get a match in your lookup.

Vendor_Map:

Mapping

LOAD

  AutoNumber( VendorName & Amount ) as Key,

  Category

Resident

  Table2

;

YourTable:

LOAD

  VendorName,

  Amount,

  Date,

  OtherColumns,

  ApplyMap( 'Vendor_Map', AutoNumber( VendorName & Amount ), 'Other' ) as Category

Resident

  Table1

;

[#5]

Hi Sunny,

Thanks for your reply.

I have tried this, it takes lot of time to load and doesnot complete the execution.

Inner join on AlphaNumeric and numeric is not quite preferred as mentioned by Krishna.

Thanks

S

[#6]

Thanks a lot Oscar.

It did work perfectly fine, only 1 thing I would like to add is that drop Table 1 at the end.

Regards,

S