topic Re: Join Issue in Connectivity & Data Prep

Join Issue

nvrphanikumar — Tue, 08 Nov 2016 14:14:38 GMT

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 ?

Re: Join Issue

rittermd — Tue, 08 Nov 2016 16:42:21 GMT

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.

Re: Join Issue

nvrphanikumar — Tue, 08 Nov 2016 16:50:01 GMT

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 ?

Re: Join Issue

sunny_talwar — Tue, 08 Nov 2016 16:59:12 GMT

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);

Re: Join Issue

rittermd — Tue, 08 Nov 2016 17:02:12 GMT

Good point. Missed that.

Re: Join Issue

nvrphanikumar — Tue, 08 Nov 2016 17:05:58 GMT

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

I have the Load Statements already.

Re: Join Issue

sunny_talwar — Tue, 08 Nov 2016 17:16:23 GMT

Not sure I understand what you mean

Re: Join Issue

nvrphanikumar — Tue, 08 Nov 2016 17:24:30 GMT

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);

Re: Join Issue

sunny_talwar — Tue, 08 Nov 2016 17:58:08 GMT

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

Re: Join Issue

nvrphanikumar — Tue, 08 Nov 2016 18:12:57 GMT

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

Re: Join Issue

nvrphanikumar — Tue, 08 Nov 2016 18:52:55 GMT

Sunny,

Finally figured out what the issue is.

During Join, I assumed that if I include all columns from all tables then data will get inserted as multiple columns. So I chose only the columns I needed in the output.

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

TARGETS

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

Which means I didn't include Join Condition columns in the statement.

After I changed the Query to below,I get correct results.

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

REPORT_DT,

JOB_OPENING_ID,

TARGETS

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

Re: Join Issue

sunny_talwar — Tue, 08 Nov 2016 22:48:42 GMT

Have you checked in your data model that it is now only storing 8 rows? I am surprised it made a difference. I mean a DISTINCT left join would have been explainable, but this is somewhat confusing. Is it a bug? I am not sure...