Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I was trying something.
Am unable to figure it out. Please help me with understanding how JOIN works.
Temp:
LOAD * INLINE [
P, Lat_p
P1, 1
P2, 2
];
JOIN
LOAD * INLINE [
C, Lat_c
C1, 3
C2, 4
];
Final:
LOAD
P,
// C,
Lat_p + Lat_c as Sum1,
Lat_p * Lat_c as Mult
RESIDENT Temp;
When I take a Straight table with P and C in Dimensions and Sum1 and Mult in Expressions, I get this.
P | C | Lat_P | Lat_C | Sum1 | Mult |
P1 | C1 | 1 | 3 | - | - |
P1 | C2 | 1 | 4 | - | - |
P2 | C1 | 2 | 3 | - | - |
P2 | C2 | 2 | 4 | - | - |
But if I put field "C" also during loading "Final", then I get what I want.
P | C | Lat_P | Lat_C | Sum1 | Mult |
P1 | C1 | 1 | 3 | 4 | 3 |
P1 | C2 | 1 | 4 | 5 | 4 |
P2 | C1 | 2 | 3 | 5 | 6 |
P2 | C2 | 2 | 4 | 6 | 8 |
Could someone help me understand why this works and the first case does not?
Many regards
Ragha
Hi,
go to these links you will get a better idea of join .
http://www.qlikviewaddict.com/2012/03/explaining-joins.html
The second link is the best and simple one .
HI,
To get the expected result.. you should put "NoConcatenate" before load "Final" table.
Check the script below..
Temp:
LOAD * INLINE [
P, Lat_p
P1, 1
P2, 2
];
JOIN
LOAD * INLINE [
C, Lat_c
C1, 3
C2, 4
];
Final:
NoConcatenate
LOAD
P,
C,
Lat_p + Lat_c as Sum1,
Lat_p * Lat_c as Mult
// Lat_p
// ,Lat_c
RESIDENT Temp;
DROP Table Temp;
hi
your first code does not work because,
Sum1 and Mult fields contain in Final table, and both field made on the basis of P and C, so, In straight table qlikview calculate Sum1 and Mult on the basis of P and C , then P and C must contain in Final table.(that is in the same table).
note- If you made any field based on calculation then fields must contain in the same table for right working.
thus this is the right code.
Temp:
LOAD * INLINE [
P, Lat_p
P1, 1
P2, 2
];
JOIN
LOAD * INLINE [
C, Lat_c
C1, 3
C2, 4
];
Final:
LOAD
P,
C,
Lat_p + Lat_c as Sum1,
Lat_p * Lat_c as Mult
RESIDENT Temp;
Hi.
Thanks for your response.
Both the sites give a good insight on JOINS.
But I still am stuck with why my problem is not getting solved in one case and gets solved in the other.
Thanks again
Hi karthiks
Thanks for your response.
If I navigate to Edit script and then to Table viewer, irrespective of NOCONCATENATE the tables and the links remain same.
My question is why should we load both Field P and Field C for getting the correct answer.
Why does not the problem work if only Field P is loaded into "FINAL"?
I am just curious on how JOIN works.
Many thanks
Hi vishwaranjan
Thanks for your response.
This is what I understand should have happened.
By the end of JOIN, there is 1 table named "Temp" which has all the 4 fields in it.
In the "Final" table, I thought it would be enough to load 1 field.
But obviously I am wrong.
I think, I kind of understand now.
Thanks again