Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
Not applicable

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

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;

5 Replies
swuehl
Not applicable

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

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

swuehl
Not applicable

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

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
Not applicable

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

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

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

Thank you very much! It worked

Not applicable

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

Thank you to you too!