Join Issue

[nvrphanikumar]

Hi,

Fairly new to Qlik,trying to figure out whats wrong with this LEFT Join.

I should have seen 8 rows from Table A but instead get a result with number of rows that exist in Table B(80K rows).

  Table_A:

  REPORT_DT,

JOB_OPENING_ID

FROM [lib://QVDs/Table_A.qvd](qvd)

WHERE JOB_OPENING_ID = '201864';

Left join (Table_A)

FROM [lib://QVDs/Table_b.qvd](qvd);

Also,

I would like Both Table_A and Table_B to join based on Report_dt and JOb_Opening_id ;

Will it automatically join based on common columns or should I create a concatenated key ?

[rittermd]

I think the problem is the semi-colon after the where clause.  Remove that and give it a try.

If you have multiple fields that match then you will get a synthetic key.  So you should concatenate them into a key.

[nvrphanikumar]

That didn't help.When I removed semi-colon like below, It throw syntax error.

WHERE JOB_OPENING_ID = '201864'

On Synthetic Keys,I wouldn't be loading the common columns again.I would select only the columns I need.

So my question is,Can JOIN Command automatically pick two common columns from each table before it joins with the other table ?

[sunny_talwar]

What are you left joining here?

You missed the LOAD statement. Try this:

Table_A:

LOAD REPORT_DT,

    JOB_OPENING_ID

FROM [lib://QVDs/Table_A.qvd](qvd)

WHERE JOB_OPENING_ID = '201864';

Left join (Table_A)

LOAD *

FROM [lib://QVDs/Table_b.qvd](qvd);

[rittermd]

Good point.  Missed that.

[nvrphanikumar]

Sorry,I should have been more specific  with my code rather than stripping it.

I have the Load Statements already.

[sunny_talwar]

Not sure I understand what you mean

[nvrphanikumar]

Sorry for the back and forth.

What I meant is that I have code similar to what you are suggesting

Table_A:

LOAD REPORT_DT,

    JOB_OPENING_ID

FROM [lib://QVDs/Table_A.qvd](qvd)

WHERE JOB_OPENING_ID = '201864';

Left join (Table_A)

LOAD *

FROM [lib://QVDs/Table_b.qvd](qvd);

[sunny_talwar]

I think Left Join will bring all the rows from Table_b where it finds the match in Table_A on the fields you are joining on? Have you checked the values are not matching for the joined fields? May be you have one addition field you need to join on?

Just as a side note, the field names need to match word-to-word and case-to-case for QlikView to know that you are joining the two tables on the intended fields

[nvrphanikumar]

The Results didn't come back right even after I chose INNER JOIN

Table A has REPORT_DT,   JOB_OPENING_ID

and Table B has REPORT_DT,    JOB_OPENING_ID

I'm assuming JOIN would automatically join based on these two Columns from two tables.

Additionally I have created a single Join Key Column on both tables but it still doesn't join the values properly.

Yes.I'm sure that The column names match exactly and Values are right