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);
thanks so much Avinash !
since i don't want to use a Flag, i just marked your response as helpul..
Thanks stalwar1,
I didn't know that we can use Exists with only 1 input..
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/
Thanks a lot !