Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
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
Hi Arvind,
Thanks for the reply
with your script i have this:
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
Hi Youssef ,
Now Go to properties -> dimension-> Select ID and check supress Null value check box
Thanks,
Arvind Patil
Hi Youssef,
Please find the attachment:
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
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
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 ?
Yes please, i want to filter all the tables. and use KEEP instead for making the tables always visible.
Thanks
find the attached app for solution