Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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!

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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;

View solution in original post

5 Replies
swuehl
MVP
MVP

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

swuehl
MVP
MVP

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;

sunny_talwar

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

[...]

Not applicable
Author

Thank you very much! It worked

Not applicable
Author

Thank you to you too!