Join with multipe OR criterias

[Anonymous]

Dear Community!

I have following table structure

Table Data

Columns :

Producer,

DepthSN

Table Depth

Columns :

Depth_lvl_1,

Depth_lvl_2,

Depth_lvl_3,

...

How should we join this tables, so that DepthSN will be related to all levels (Depth_lvl_1 ...).

So if we select DepthSN and it exits only in Depth_lvl_2 the whole row of table depth with the matched DepthSN will be presented.

I think it should be something like join  DepthSN = Depth_lvl_1 OR  Depth_lvl_2 OR Depth_lvl_3 ???

Please help!

[anbu1984]

Data:
Load *,DepthSN As DepthSN_New Inline [
Producer, DepthSN
p1,aaa
p2,bbb
p3,ccc ];


Depth:
Load *,Pick(IterNo(),Depth_lvl_1,Depth_lvl_2,Depth_lvl_3) As DepthSN_New While IterNo() <= 3;
Load * Inline [
Depth_lvl_1,Depth_lvl_2,Depth_lvl_3
aaa,aaa1,aaa2
bbb1,bbb,bbb2
ccc1,ccc2,ccc ];

[jonathandienst]

Hi

You will need a generic link table, something like this:

Data:

LOAD

  Id,

  Producer,

  Quantity,

  DepthSN

Inline [

  Id, Producer, Quantity, DepthSN

  1, AA, 22, L1A

  2, BB, 21, L1B

  2, CC, 23, L1C

  3, DD, 24, L2A

  4, BB, 20, L2B

  5, EE, 26, L3A

  6, BB, 23, L3B

];

Depth:

LOAD

  Ref,

  Depth_lvl_1,

  Depth_lvl_2,

  Depth_lvl_3,

  Depth_lvl_1 & Depth_lvl_2 & Depth_lvl_3 As %Key

Inline [

  Ref, Depth_lvl_1, Depth_lvl_2, Depth_lvl_3

  1966, L1A, L2A, L3A

  9365, L1B, L2B, L3B

  4422, L1C, L2C, L3C

];

GenericLink:

LOAD Depth_lvl_1,

  Depth_lvl_2,

  Depth_lvl_3,

  %Key,

  Depth_lvl_1 As DepthSN

Resident Depth;

Concatenate(Link)

LOAD Depth_lvl_1,

  Depth_lvl_2,

  Depth_lvl_3,

  %Key,

  Depth_lvl_2 As DepthSN

Resident Depth;

Concatenate(Link)

LOAD Depth_lvl_1,

  Depth_lvl_2,

  Depth_lvl_3,

  %Key,

  Depth_lvl_3 As DepthSN

Resident Depth;

DROP Fields Depth_lvl_1,

  Depth_lvl_2,

  Depth_lvl_3

FROM Depth;

Replace the LOADs for Data and Depth with your actual load statements.

HTH

Jonathan

[Anonymous]

Thank you Anbu and Jonathan!

Both solutions worked.

Anbus solution creates a lot of additional data as the Data table is multiplied by the number of Depth levels of the Depth table.

I am not sure if this is also happening, when using Jonathans solution?