Non Equi Join in Qlikview

[Anonymous]

This is something I have searched a lot in Qlikview. Haven't found any solution for this.

Say, I have 2 Tables with the following Data

A:

Key	Value
10	150
11	100

B:

Key	Value
10	150
11	80

In SQL, If I do an Inner Join with On A.Key = B.Key and A.Value <> B.Value, I get

Key	Value
11	100
11	80

How can I achieve this in Qlikview?

[Anil_Babu_Samineni]

You can run same script until unless script was complex. Because, Qlik handles SQL direct queries without any problem...

[Anonymous]

I have to do this on QVDs

[Kushal_Chawda]

A:

LOAD Key

          Value

FROM A;

inner join(A)

LOAD Key,

           Value as VALUE

FROM B

[its_anandrjs]

What out put you expected?

[antoniotiman · Accepted solution]

Hi Koushik,

try this

A:
LOAD *,AutoNumber(Key&Value) as KeyA Inline [
Key,Value
10,150
11,100];
Join LOAD *,AutoNumber(Key&Value)as KeyB Inline [
Key,Value
10,150
11,80];
NoConcatenate LOAD Key,Value
Resident A
Where KeyA <> KeyB;
Drop Table A;

Regards,

Antonio

[sumeet-vaidya]

Hi Koushik,

This can be achieved by below 2 steps.

1. Outer Join 2 tables

2. In Resident load, Value1 <> Value2

Please find attached qvw for your reference.

Regards

Sumeet Vaidya

[Anil_Babu_Samineni]

Ok, Then use Autonumber() for 2 tables and give NoConcatenate where those two fields which we done for autonumber? If you need to help in script i will help you but try from your end

[Anil_Babu_Samineni]

Antonio gave script too

[florentina_doga]

it can be in several steps.

pls see attach