topic Re: Right Join - How do I get the excluded records in QlikView

Right Join - How do I get the excluded records

Wed, 04 Oct 2017 15:32:21 GMT

Hi,

I am using a right join on 2 tables and I'm getting the expected results. However, I'm trying to work out how I can get the excluded results. I've created a sample scenario to explain my query:

I have 2 tables as follows:

Table 1 Table 2

Code Name Code Name

111 John 111 John

112 Paul 112 Paul

113 George 113 George

114 Pete 115 Ringo

When I right join Table 2 to Table 1 I get the following, which is correct:

Table 1

Code Name

111 John

112 Paul

113 George

115 Ringo

What I would like to get is a table of what was excluded:

Table 1

Code Name

114 Pete

Can anyone advise me if and how this is possible?

Thanks.

Re: Right Join - How do I get the excluded records

sunny_talwar — Wed, 04 Oct 2017 15:38:37 GMT

May be this

Table2:

LOAD Code

FROM...

Table1:

NoConcatenate

LOAD Code,

Name

FROM

Where Not Exists(Code);

DROP Table Table2;

Re: Right Join - How do I get the excluded records

Peter_Cammaert — Wed, 04 Oct 2017 15:48:48 GMT

Not using a JOIN I guess. A JOIN throws stuff together that has at least one value in common (no values in common is to be avoided)

Try with a WHERE Not Exists() clause. You'll need either a field that uniquely identifies each individual records, or a composite key which you can create when you first load the data. Let's assume that the Code field can serve as Primary Key, then when you first load the data for Table 1 you can use something like:

Table1:

LOAD *

FROM ... (...)

WHERE Not Exists(Code);

If Table1 is loaded from a resident table, you cannot use this trick because the values of the two Code field instances will be merged into the same symbol table. Exists() looks into the field symbol table to check whether a value has already been loaded. As a result, all values will always exist and no records will be stored in Table1. You'll first have to rename the Code field in the Table1 source table.

Re: Right Join - How do I get the excluded records

its_anandrjs — Wed, 04 Oct 2017 15:52:13 GMT

Check this out and try to load table this way

T1:

LOAD * INLINE [

Code, Name

111, John

112, Paul

113, George

114, Pete

];

T2:

Concatenate

LOAD * INLINE [

Code, Name

111, John

112, Paul

113, George

115, Ringo

];

Left Join(T1)

LOAD Name ,Count(Code) as Freq Resident T1 Group By Name;

NoConcatenate

LOAD * Resident T1 Where Freq=1;

DROP Table T1;

Re: Right Join - How do I get the excluded records

Wed, 04 Oct 2017 16:46:19 GMT

Hi Sunny,

Your answer worked perfectly on my sample data. However, when I applied it to my real data it didn't work. The table returns no entries and I don't know why. The table has 5 columns but the scenario is the same. There is a unique code which identifies each record. Any suggestions why or what I should look out for? Have I given you enough information?

Thanks,

Eamonn.

Re: Right Join - How do I get the excluded records

sunny_talwar — Wed, 04 Oct 2017 16:48:42 GMT

Where not exists works in a way that it checks for a value if it exits in a field, no matter which table we are looking at.... Do you think that may be the code exists in that field from another table?

Re: Right Join - How do I get the excluded records

Thu, 05 Oct 2017 08:43:34 GMT

Hi Sunny,

I found that the format of the source was not consistent. When I changed it the script code you gave me works.

Thanks for the help,

Eamonn.