keep joins with matching values on two differents keys

[YoussefBelloum]

Hi All,

I have three tables: INVOICE, MAT, CUST with values like this:

INVOICE:

ID,     ID_CUST,     ID_MAT

1,     20,                  30

2,     21,                  31

3,     22,                  32

4,     23,                  33

5,     24,                  34

6,     25,                  35

7,     26,                  36

8,     27,                  37

9,     28,                  38

10,   29,                  39

MAT:

ID_MAT

33

34

35

36

37

38

39

40

CUST:

ID_CUST

20

21

22

23

24

25

26

when in load these table with INNER KEEPs on the Invoice i should have this:

INVOICE:

ID,     ID_CUST,     ID_MAT

1,     20,                  30

2,     21,                  31

3,     22,                  32

4,     23,                  33

5,     24,                  34

6,     25,                  35

7,     26,                  36

8,     27,                  37

9,     28,                  38

10,   29,                  39

BUT, In my final Fact table (Invoice) I want to have ONLY  the lines matching with these 3 tables, like this:

INVOICE:

ID,     ID_CUST,     ID_MAT

1,     20,                  30  //not this line

2,     21,                  31  //not this line

3,     22,                  32  //not this line

4,     23,                  33  //THIS LINE IS OK

5,     24,                  34  //THIS LINE IS OK

6,     25,                  35  //THIS LINE IS OK

7,     26,                  36  //THIS LINE IS OK

8,     27,                  37  //not this line

9,     28,                  38  //not this line

10,   29,                  39  //not this line

I tried with INNER KEEPs, with WHERE EXISTS.. but i could not get the result i want.

Please take a look

Thanks,

YB

[sunny_talwar · Accepted solution]

May be there is a better way to do this, but this seem to work

INVOICE:

LOAD * INLINE [

    ID, ID_CUST, ID_MAT

    1, 20, 30

    2, 21, 31

    3, 22, 32

    4, 23, 33

    5, 24, 34

    6, 25, 35

    7, 26, 36

    8, 27, 37

    9, 28, 38

    10, 29, 39

];

Inner Join (INVOICE)

LOAD * INLINE [

    ID_CUST

    20

    21

    22

    23

    24

    25

    26

];

Inner Join (INVOICE)

LOAD * INLINE [

    ID_MAT

    33

    34

    35

    36

    37

    38

    39

    40

];

CUST:

LOAD * INLINE [

    ID_CUST

    20

    21

    22

    23

    24

    25

    26

] Where Exists(ID_CUST);

MAT:

LOAD * INLINE [

    ID_MAT

    33

    34

    35

    36

    37

    38

    39

    40

] Where Exists(ID_MAT);

[YoussefBelloum]

thanks so much Avinash !

since i don't want to use a Flag, i just marked your response as helpul..

[YoussefBelloum]

Thanks stalwar1,

I didn't know that we can use Exists with only 1 input..

[sunny_talwar]

Yes you can... as long as the field name between the two tables match, you can use it using a single field name. In fact, when you are loading from a qvd file, it is better to use it with single field name as it keeps the load optimized

https://www.quickintelligence.co.uk/qlikview-optimised-qvd-loads/

[YoussefBelloum]

Thanks a lot !