topic join on non equal condition ? in QlikView

join on non equal condition ?

Sun, 19 Jun 2011 06:32:17 GMT

Hello All,

I have two Tables A and B. Both have fields Weeks and Demand. Now I want to concatenate these two tables such that All the rows are received from Table A and Tables B have only those rows, where week is more than the maximum week in table A.

Table A:

Weeks Demands

W1 2

W2 4

W3 6

W4 8

W5 10

Table B

Weeks Demands

W1 8

W2 5

W3 3

W4 7

W5 20

W6 18

W7 12

[A+b]:

Weeks Demands

W1 2

W2 4

W3 6

W4 8

W5 10

W6 18

W7 12

How can i achieve this? It should be dynamic so that, no matter how many weeks are in table A and how many weeks are in table B, they should be concatenated this week. The above is just a sample data

Arif

Re: join on non equal condition ?

Sun, 19 Jun 2011 07:41:05 GMT

Hi Arif,

I Suggest:

[Table A]:
LOAD * INLINE [
WeeksA, DemandsA
W1, 2
W2, 4
W3, 6
W4, 8
W5, 10
];

[Table B]:
LOAD * INLINE [
WeeksB, DemandsB
W1, 8
W2, 5
W3, 3
W4, 7
W5, 20
W6, 18
W7, 12
];

MAXWEEK:
LOAD MaxString(WeeksA) as maxweek RESIDENT [Table A];

[AB]:
LOAD WeeksA as Weeks, DemandsA as Demands
RESIDENT [Table A];

CONCATENATE
LOAD WeeksB as Weeks, DemandsB as Demands
RESIDENT [Table B]
WHERE WeeksB > peek('maxweek', 0, 'MAXWEEK')
;

DROP TABLE MAXWEEK;

hth Jeroen

join on non equal condition ?

kaushiknsolanki — Sun, 19 Jun 2011 07:44:22 GMT

Hi,

You can use the exists() Function.

For example

Table:

LOAD

Weeks, Demands

FROM TableA;

LOAD
Weeks, Demands

FROM TableB

WHERE NOT EXISTS (Weeks);

This will load all data from table A but loads only those Weeks from table B which doesnt exist in Table A.

Hope this will help you.

Regards,

Kaushik Solanki