Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
biob_stein
Contributor

Join with multipe OR criterias

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!

3 Replies
anbu1984
Honored Contributor III

Re: Join with multipe OR criterias

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 ];

MVP
MVP

Re: Join with multipe OR criterias

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
biob_stein
Contributor

Re: Join with multipe OR criterias

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?

Community Browser