topic Re: An issue with joining tabels. in QlikView

An issue with joining tabels.

Thu, 25 Oct 2012 20:04:59 GMT

I have two tables

For one of the Episodes of Sienfield

First table has this info

Title Name, Episode Number, Title ID, Actor Info, Amt. owed to Distributor, Length of the Show etc.,

second table has the following info

Title ID, Release Number

I am trying to join these tables to get the Release Number from the second table.

Why is it listing all the rows from the second table even though the join automatically happens on the TItle ID?

Table 1 data

Sienfield 1, 1 100,xxxx,1000, 30

Sienfield 2, 2 100,tyty,1000, 30

Sienfield 3, 3 100,hggh,1000, 30

Sienfield 4, 4 100,xxxxx,1000, 30

Sienfield 5, 5 100,xxxx,1000, 30

Sienfield 6, 6 100,xxxx,1000, 30

Table 2 data

100, 1999

101, 2000

102,2012

103,2003

When I join on TItle ID, I am expecting the release year to come as 1999. But it is displaying all the release years.

This is how I am coding this. I thought the join would happen autoamatically on the titleid and 1 row will be returned for the releaseyear of title 100. Can some one help me what the problem is

title:

LOAD
"TITLE_ID" as titleid,

titlename,

amt

;
SQL SELECT "titleid","titlename",amt
FROM "T_TITLE_info";

releaseyear:

LOAD "RELEASE_YEAR" as releaseyear,

"TITLE_ID" as titleid;

SQL SELECT "RELEASE_YEAR",

"TITLE_ID"

FROM "T_TITLE";

Re: An issue with joining tabels.

MayilVahanan — Thu, 25 Oct 2012 21:55:04 GMT

Use left join. For example:

Load * Inline

[

TitleName, EpisodeNumber, TitleID, ActorInfo, Amt.owedtoDistributor, Length

Sienfield 1, 1, 100,xxxx,1000, 30

Sienfield 2, 2, 100,tyty,1000, 30

Sienfield 3, 3, 100,hggh,1000, 30

Sienfield 4, 4, 100,xxxxx,1000, 30

Sienfield 5, 5, 100,xxxx,1000, 30

Sienfield 6, 6, 100,xxxx,1000, 30

];

Left join

LOAD * Inline

[

TitleID,Year

100, 1999

101, 2000

102,2012

103,2003

];

in this Year gives 1999 alone..

Regards,

R.MayilVahanan

Re: An issue with joining tabels.

CELAMBARASAN — Thu, 25 Oct 2012 22:00:32 GMT

You mean you don't want to load the Non existing Title ID's?

title:

LOAD
"TITLE_ID" as titleid,

titlename,

amt

;
SQL SELECT "titleid","titlename",amt
FROM "T_TITLE_info";

releaseyear:

LOAD "RELEASE_YEAR" as releaseyear,

"TITLE_ID" as titleid Where Exists(titleid,TITLE_ID);

SQL SELECT "RELEASE_YEAR",

"TITLE_ID"

FROM "T_TITLE";

Re: An issue with joining tabels.

jldengra — Fri, 26 Oct 2012 07:29:57 GMT

The join does not "automatically happen" as you say.

Your tables are being joined by the field Title ID, not by inner join but by outer join, so QV is displaying all the release years, even not related to any record in the model.

In order to make an INNER join, you need to do it explicitly into your script.

Let be EPISODES the name of the first table you load, then you need to state INNER JOIN (EPISODES) before the load of the related release years in your script:

EPISODES:

LOAD ....

.......;

INNER JOIN (EPISODES)

LOAD .......

.........;

I hope this helps.

Greetings,

J.L.Dengra

Re: An issue with joining tabels.

Fri, 26 Oct 2012 13:10:12 GMT

Dengra, Thanks for your response.

I have my code in qvw as follows

EPISODES:

LOAD ....

SELECT title_id,title_name,amt, episode_no from t_itle

RELEASENUMBER:

LOAD

SELECT titile_id,release_number rom t_release

Store EPISODES into QVDGenerator\episodes.qvd;

Store RELEASENUMBER:into QVDGenerator\release.qvd;

I am confused as to where I can add the join. When I add it before the load under ReleaseNumber, I am getting an error. Can you please guide me through that? Thanks

Re: An issue with joining tabels.

MayilVahanan — Fri, 26 Oct 2012 14:13:19 GMT

EPISODES:

LOAD *;

SELECT title_id,title_name,amt, episode_no from t_itle;

Inner Join(EPISODES) // else Left Join()

LOAD *;

SELECT titile_id,release_number from t_release;

Store EPISODES into QVDGenerator\episodes.qvd(qvd);

Store RELEASENUMBER:into QVDGenerator\release.qvd(qvd);

Hope that helps

Re: An issue with joining tabels.

Fri, 26 Oct 2012 14:47:44 GMT

Can some one please provide me the syntax for inner join in the below scenario? There are tables from two different databases. I only want to get the release_number from t_release table for the specified title_id in the first table and I am not sure where to add the join.

EPISODES:

LOAD ....

SELECT title_id,title_name,amt, episode_no from t_itle

RELEASENUMBER:

LOAD

SELECT titile_id,release_number rom t_release

Store EPISODES into QVDGenerator\episodes.qvd;

Store RELEASENUMBER:into QVDGenerator\release.qvd;

Re: An issue with joining tabels.

jldengra — Fri, 26 Oct 2012 15:25:59 GMT

I think you should only model a table: EPISODES, with its QVD, but not for RELEASENUMBER.

If you still need to keep a separate table for RELEASENUMBER, it is possible, but you would need to rename the fields to avoid a synthetic key error.

This is the resulting code modifying the sample from Mayil Vahanan Ramasamy , assuming that you need to store only the related releases, otherwise you should not include the last inner join:

EPISODES:
Load * Inline
[
TitleName, EpisodeNumber, TitleID, ActorInfo, Amt.owedtoDistributor, Length
Sienfield 1, 1, 100,xxxx,1000, 30
Sienfield 2, 2, 100,tyty,1000, 30
Sienfield 3, 3, 100,hggh,1000, 30
Sienfield 4, 4, 100,xxxxx,1000, 30
Sienfield 5, 5, 100,xxxx,1000, 30
Sienfield 6, 6, 100,xxxx,1000, 30
];

RELEASES:
LOAD TitleID as TitleID_releases, Year as Year_releases Inline
[
TitleID, Year
100, 1999
101, 2000
102,2012
103,2003
];

INNER JOIN (EPISODES)
LOAD DISTINCT TitleID_releases as TitleID
RESIDENT RELEASES;

// Only if releases need to be limited to related to episodes
INNER JOIN (RELEASES)
LOAD DISTINCT TitleID as TitleID_releases
RESIDENT EPISODES;

Re: An issue with joining tabels.

Fri, 26 Oct 2012 16:04:19 GMT

Should the inner join go under Episodes or ReleaseNumber tag?

Re: An issue with joining tabels.

MayilVahanan — Fri, 26 Oct 2012 19:55:01 GMT

Please check the attached file .

Hope it helps