topic Re: Join with multipe OR criterias in QlikView

Join with multipe OR criterias

Anonymous — Wed, 08 Apr 2015 15:58:49 GMT

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!

Re: Join with multipe OR criterias

anbu1984 — Thu, 09 Apr 2015 05:09:28 GMT

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

Re: Join with multipe OR criterias

jonathandienst — Thu, 09 Apr 2015 06:23:11 GMT

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

Re: Join with multipe OR criterias

Anonymous — Mon, 13 Apr 2015 15:23:41 GMT

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?