topic Re: Using "where exists" to match fields between two tables. in QlikView

Using "where exists" to match fields between two tables.

Tue, 03 Jan 2017 22:24:30 GMT

Hello! I am trying to reduce some massive tables (with Basketball teams results mainly) to small ones (I have them for several countries and I only need one of them right now), as well as the tables with the teams.

I manage to reduce the table with the matches with a simple "Wildmatch" using the country code, to reduce the second table I thought of using where exists and the Field "Team ID" since this field is the link between tables. But if I do it as it follows, I get the full table and (even though I am pretty new in QlikView) I understand why.

Is there any way to use something like "where exists ([Team ID]) in MATCH" ? So when I am creating "TEAMS_2" it only takes the teams which Team ID is in the reduced "MATCH_2" table?

I know it'd be easier to do if I had the field "Country" or something like that in TEAMS but because of how we get the info it is impossible.

MATCH_2:

NoConcatenate

LOAD *

Resident MATCH

where WildMatch (Country ,'SPAIN')>0 ;

Drop Table MATCH;

TEAMS_2:

NoConcatenate

LOAD *

Resident TEAMS

where Exists ([Team ID] ) ;

[...]

Thank you in advance!

Re: Using "where exists" to match fields between two tables.

swuehl — Tue, 03 Jan 2017 22:50:27 GMT

Not sure I understand your issue. Are there any other tables involved in your script code?

Re: Using "where exists" to match fields between two tables.

swuehl — Tue, 03 Jan 2017 22:53:14 GMT

Ah, I think I understand. You are using RESIDENT LOADs and Exists() will consider all field values loaded so far, not looking at specific table loads.

Maybe you can use Exists() with the two argument version:

MATCH_2:

NoConcatenate

LOAD *,

[Team ID] as TeamIDFilter

Resident MATCH

where WildMatch (Country ,'SPAIN')>0 ;

Drop Table MATCH;

TEAMS_2:

NoConcatenate

LOAD *

Resident TEAMS

where Exists (TeamIDFilter, [Team ID] ) ;

DROP FIELD TeamIDFilter;

Re: Using "where exists" to match fields between two tables.

sunny_talwar — Tue, 03 Jan 2017 22:53:56 GMT

If you are dropping MATCH and there is not other reference of Team ID elsewhere, I would assume that the only Team ID that would come from TEAMS table should be the ones that are in MATCH_2. Although, this is not required, you can try this this:

MATCH_2:

NoConcatenate

LOAD *,

[Team ID] as [Temp Team ID]

Resident MATCH

where WildMatch (Country ,'SPAIN') > 0;

Drop Table MATCH;

TEAMS_2:

NoConcatenate

LOAD *

Resident TEAMS

Where Exists([Temp Team ID], [Team ID]);

[...]

Re: Using "where exists" to match fields between two tables.

Tue, 03 Jan 2017 23:45:07 GMT

Thank you very much! It worked

Re: Using "where exists" to match fields between two tables.

Tue, 03 Jan 2017 23:45:19 GMT

Thank you to you too!