join on non equal condition ?

[#1]

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

[#2]

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

[kaushiknsolanki]

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