topic Re: Trouble with joins I think in QlikView

Trouble with joins I think

mwscott1 — Fri, 20 Nov 2015 16:49:19 GMT

I have two tables (see below). With one entry on each the results are fine. However, when a second entry is added to table two the results are not what I want. The two tables are LEFT JOINED by REF Number...

Example 1

Table1		Table2			Results

REF Number	Candidate Submitted	REF Number	Candidate Interview	Candidate Appointed	REF Number	Candidate Submitted	Candidate Interview	Candidate Appointed
R0001	1	R0001	5		R0001	1	5

Example 2

Table1		Table2			Results

REF Number	Candidate Submitted	REF Number	Candidate Interview	Candidate Appointed	REF Number	Candidate Submitted	Candidate Interview	Candidate Appointed
R0001	1	R0001	5		R0001	1	5
		R0001		10	R0001	1		10

I would like the results to be either of the two ways.

Results

REF Number	Candidate Submitted	Candidate Interview	Candidate Appointed
R0001	1	5	10

Results

REF Number	Candidate Submitted	Candidate Interview	Candidate Appointed
R0001	1
R0001		5
R0001			10

Is this possible?

Re: Trouble with joins I think

Anonymous — Fri, 20 Nov 2015 17:56:04 GMT

Take a look at the attached file.

Regards,

Gabriel

Re: Trouble with joins I think

swuehl — Fri, 20 Nov 2015 18:07:40 GMT

Maybe like this:

Table1:
LOAD * INLINE [
    REF_Number, Candidate_Submitted
    R0001, 1
];


Concatenate (Table1)
LOAD REF_Number,
  If(isnum(Candidate_Interview),Candidate_Interview,NULL()) as Candidate_Interview, //only needed to create NULL for missing values
  if(IsNum(Candidate_Appointed),Candidate_Appointed,NULL()) as Candidate_Appointed //only needed to create NULL for missing values
INLINE [
    REF_Number, Candidate_Interview, Candidate_Appointed
    R0001,5,
    R0001,  ,10
];


NoConcatenate
Result:
Load
  REF_Number,
  Only(Candidate_Submitted) as Candidate_Submitted,
  Only(Candidate_Interview) as Candidate_Interview,
  Only(Candidate_Appointed) as Candidate_Appointed
Resident Table1
GROUP BY REF_Number;


DROP Tables Table1;

Re: Trouble with joins I think

mwscott1 — Fri, 20 Nov 2015 19:48:08 GMT

OK I might have not have done a good job explaining what I am working with. Here is my script I have so far and some sample data attached...

REFERRALS:
LOAD Upper (F3) as "Name",
Upper (F4) as "Address",
Upper (F5) as "Phone",
Upper (F6) as "City",
Upper (F7) as "State",
Upper (F8) as "Zip",
Upper (F9) as "P Name",
Upper (F10) as "P Number",
Upper (F11) as "P Email",
Upper (F12) as "P Phone",
Upper (F13) as "Email",
Upper (F14) as "REF Number",
Upper (F15) as "Candidate Submitted"
FROM

(biff, embedded labels, table is Sheet1$);

LEFT JOIN

TR:
LOAD F3 as "REF Number",
F4 as "Candidate Interview",
F5 as "Candidate Appointed"
FROM

FROM

(biff, embedded labels, table is Sheet1$);

Re: Trouble with joins I think

swuehl — Fri, 20 Nov 2015 20:49:54 GMT

Hm, these sample files don't show multiple "REF Number" values, so there shouldn't be a problem with the JOIN at all?

Re: Trouble with joins I think

mwscott1 — Fri, 20 Nov 2015 21:12:33 GMT

I have attached a new TR file. the issue is the Candidate Submitted value of 1 is populated on the Referral Table the Candidate Interview value of 5 and Candidate Appointed value of 10 are populated on the TR table. If the Candidate Interview value of 5 and Candidate Appointed value of 10 are both added at the same time everything is fine, because there is a one to one entry on each table like Donald Duck in the Example below. However, if Candidate Interview value of 5 in populated on the TR table and then Candidate Appointed value of 10 are populated on the TR table there are 3 separate entries for the same REF Number. In the results it combines two entries but not the third which results in 2 lines and causes an extra 1 for Candidate Submitted. Example Jan Smith and John Doe

Name	REF Number	Candidate Submitted	Candidate Interview	Candidate Appointed
DONALD DUCK	R0004	1	5	10
JAN SMITH	R0002	1		10
JAN SMITH	R0002	1	5
JOHN DOE	R0001	1		10
JOHN DOE	R0001	1	5
MICKEY MOUSE	R0003	1	5

Re: Trouble with joins I think

swuehl — Fri, 20 Nov 2015 21:51:49 GMT

I am still assuming my previous approach should work, with your data, something along these lines:


REFERRALS:
LOAD Name, 
     Address, 
     Phone, 
     City, 
     State, 
     [Zip Code], 
     [P Name], 
     [P Number], 
     [P Email], 
     [P Phone], 
     Email, 
     [REF Number], 
     [Candidate Submitted]
FROM
[Referrals.xls]
(biff, embedded labels, table is Sheet1$);




LEFT JOIN
LOAD [REF Number], 
     Only([Candidate Interview]) as [Candidate Interview], 
     Only([Candidate Appointed]) as   [Candidate Appointed]
FROM
[TR (1).xls]
(biff, embedded labels, table is Sheet1$)
GROUP BY [REF Number];

Re: Trouble with joins I think

MarcoWedel — Fri, 20 Nov 2015 22:22:58 GMT

Hi,

another solution could be:

REFERRALS:
LOAD Name,
    Address,
    Phone,
    City,
    State,
    [Zip Code],
    [P Name],
    [P Number],
    [P Email],
    [P Phone],
    Email,
    [REF Number],
    [Candidate Submitted]
FROM [https://community.qlik.com/servlet/JiveServlet/download/916959-197908/Referrals.xls] (biff, embedded labels, table is Sheet1$);

LEFT JOIN

TR:
LOAD [REF Number],
    [Candidate Interview]
FROM [https://community.qlik.com/servlet/JiveServlet/download/917024-197940/TR.xls] (biff, embedded labels, table is Sheet1$)
Where Len([Candidate Interview]);

LEFT JOIN

TR:
LOAD [REF Number],
    [Candidate Appointed]
FROM [https://community.qlik.com/servlet/JiveServlet/download/917024-197940/TR.xls] (biff, embedded labels, table is Sheet1$)
Where Len([Candidate Appointed]);

hope this helps

regards

Marco