Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
Not sure I understand your issue. Are there any other tables involved in your script code?
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;
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]);
[...]
Thank you very much! It worked
Thank you to you too!