Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
YoussefBelloum
Champion
Champion

keep joins with matching values on two differents keys

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

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

14 Replies
arvind_patil
Partner - Specialist III
Partner - Specialist III

Hi Youssef;

Please find below code:

LOAD * Inline [

ID_CUST

20

21

22

23

24

25

26];

Left Join

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

];

Right Join

LOAD * Inline [

ID_MAT

33

34

35

36

37

38

39

40

];

Thanks,

Arvind Patil

YoussefBelloum
Champion
Champion
Author

Hi Arvind,

Thanks for the reply

with your script i have this:

screen.png

The first problem here: I still have the excluded values on the ID_MAT on a table box

the second problem is that here i have only one table in the model, i want to have my fact table linked to the dimensions (Material and Customers).

Thanks,

YB

arvind_patil
Partner - Specialist III
Partner - Specialist III

Hi  Youssef ,

Now Go to properties -> dimension-> Select ID and check supress Null value check box

Supress Null.png

Thanks,

Arvind Patil

arvind_patil
Partner - Specialist III
Partner - Specialist III

Hi Youssef,

Please find the attachment:

YoussefBelloum
Champion
Champion
Author

I see the app, but there is always one table (i need 3 like described on the thread).. and i want to really filter all the values on the script

avinashelite

So you want to eliminate the values from Dimension tables also ??? that is INVOICE: and MAT tables ...The values which your seeing are the values from the actual dimensions  table .....the tables is reduce but since you have linked the tables it getting the values from the other tbale

YoussefBelloum
Champion
Champion
Author

I replaced the joins with KEEP and it is ok for the model, i have my 3 tables now. but why in the dimensions tables the excluded lines is always visible ?

YoussefBelloum
Champion
Champion
Author

Yes please, i want to filter all the tables. and use KEEP instead for making the tables always visible.

Thanks

avinashelite

find the attached app for solution