topic Re: Inner Join table with 2 matching column condition in QlikView

Inner Join table with 2 matching column condition

Wed, 25 Nov 2020 16:16:04 GMT

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,

Re: Inner Join table with 2 matching column condition

sunny_talwar — Wed, 20 Apr 2016 15:23:09 GMT

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.

Re: Inner Join table with 2 matching column condition

Qrishna — Wed, 20 Apr 2016 15:30:33 GMT

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.

Re: Inner Join table with 2 matching column condition

oscar_ortiz — Wed, 20 Apr 2016 17:59:33 GMT

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

;

Re: Inner Join table with 2 matching column condition

Thu, 21 Apr 2016 10:58:08 GMT

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

Re: Inner Join table with 2 matching column condition

Thu, 21 Apr 2016 10:59:30 GMT

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,