Understanding "JOIN" better.

[#1]

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

[sujeetsingh]

Hi,

go to these links you will get a better idea of join .

http://qlikshare.com/877

http://www.qlikviewaddict.com/2012/03/explaining-joins.html

The second link is the best and simple one .

[#3]

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;

[#4]

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;

[#5]

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

[#6]

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

[#7]

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