Trouble with joins I think

[mwscott1]

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

or

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

Is this possible?

[Anonymous]

Hi

Take a look at the attached file.

Regards,

Gabriel

[swuehl]

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;

[mwscott1]

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

[swuehl]

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

[mwscott1]

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

[swuehl]

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

[MarcoWedel · Accepted solution]

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