Incorrect model generating lots of duplicates - Spot the error

[spook2020202]

Hi,

I'm trying to create my model using script but cant seem to be getting something wrong as my table visual is showing lots of records that dont technically exist.

My data connection runs in a for loop and makes multiple API calls to get fixture data.
When the API is called once it works fine. when called from within the loop however, the data model seems to get a few extra tables and my data seems to be mapping incorrectly.

LIB CONNECT TO 'Matches for Leagues';

Let j=0;

for j = 0 to 5

Let vElementID = peek('league_id', $(j), 'leagues');

RestConnectorMasterTable:
SQL SELECT 
	"__KEY_api",
	(SELECT 
		"fixture_id",
		"league_id",
		"event_date",
		"event_timestamp",
		"firstHalfStart",
		"secondHalfStart",
		"round",
		"status",
		"statusShort",
		"elapsed",
		"venue",
		"referee",
		"goalsHomeTeam",
		"goalsAwayTeam",
		"__KEY_fixtures",
		"__FK_fixtures",
		(SELECT 
			"team_id",
			"team_name",
			"logo",
			"__FK_homeTeam"
		FROM "homeTeam" FK "__FK_homeTeam"),
		(SELECT 
			"team_id" AS "team_id_u0",
			"team_name" AS "team_name_u0",
			"logo" AS "logo_u0",
			"__FK_awayTeam"
		FROM "awayTeam" FK "__FK_awayTeam"),
		(SELECT 
			"halftime",
			"fulltime",
			"extratime",
			"penalty",
			"__FK_score"
		FROM "score" FK "__FK_score")
	FROM "fixtures" PK "__KEY_fixtures" FK "__FK_fixtures")
FROM JSON (wrap off) "api" PK "__KEY_api"
WITH CONNECTION(
Url "<a href="<a href="https://api-football-v1.p.rapidapi.com/v2/fixtures/league/$(vElementID" target="_blank">https://api-football-v1.p.rapidapi.com/v2/fixtures/league/$(vElementID</a>" target="_blank"><a href="https://api-football-v1.p.rapidapi.com/v2/fixtures/league/$(vElementID</a" target="_blank">https://api-football-v1.p.rapidapi.com/v2/fixtures/league/$(vElementID</a</a>>)"
);

[fixtures]:
LOAD	[fixture_id],
	[league_id],
	[event_date],
	[event_timestamp],
	[firstHalfStart],
	[secondHalfStart],
	[round],
	[status],
	[statusShort],
	[elapsed],
	[venue],
	[referee],
	[goalsHomeTeam],
	[goalsAwayTeam],
	[__KEY_fixtures]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_fixtures]);

[homeTeam]:
Inner Join(fixtures)
LOAD	[team_id] as [hometeam_id],
	[team_name] as [hometeam_name],	
	[__FK_homeTeam] AS [__KEY_fixtures]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_homeTeam]);


[awayTeam]:
Inner Join(fixtures)
LOAD	[team_id_u0] as [awayteam_id],
	[team_name_u0] as [awayteam_name],	
	[__FK_awayTeam] AS [__KEY_fixtures]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_awayTeam]);


[score]:
Inner Join(fixtures)
LOAD	[halftime],
	[fulltime],
	[extratime],
	[penalty],
	[__FK_score] AS [__KEY_fixtures]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_score]);

NEXT j;


DROP TABLE RestConnectorMasterTable;

exit Script;

The issue thats happening is that all home teams are showing against all away teams when put into a table visual so it's obvious there is some kind of model issue. in the screenshot below its going Ajaccio as having played many game (that didnt happen) lots of them on the same day/time.

 Is anyone able to spot my mapping error. I've tried several changes but all have the same result.

Thanks.

[spook2020202]

Seems to be an issue with the for loop. When I remove the for loop and just run the connection is works fine.
Can't see why it would create those extra tables. What I'm aiming for it to look like is:

[Vegar]

The issue lies in the combination of loop and join.

A solution for this could be to skip the Joins during the loop. Perform the Joins after this loop is finished.

[Vegar]

Another way to solve it is to do the following.
LIB CONNECT TO 'Matches for Leagues';
Let j=0;
for j = 0 to 5
Let vElementID = peek('league_id', $(j), 'leagues');

RestConnectorMasterTable:
SQL SELECT
...
FROM JSON (wrap off) ...
WITH CONNECTION();

[fixtures]:
NoConcatenate
LOAD [fixture_id],
[...]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_fixtures]);

[homeTeam]:
Inner Join(fixtures)
LOAD [...]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_homeTeam]);


[awayTeam]:
Inner Join(fixtures)
LOAD [...]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_awayTeam]);


[score]:
Inner Join(fixtures)
LOAD [...]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_score]);

FinalizeFixture:
Load
Field1,
Field2,
[...]
FieldN
RESIDENT fixtures;
DROP TABLE fixtures;
NEXT j;
DROP TABLE RestConnectorMasterTable;
exit Script;

[spook2020202]

Thanks Vegar,

I've tried for a couple of hours now to get your proposed solution working but while it corrected my model it creates a lot of duplicate values. I normally see this if I do a join incorrectly in SQL (using the wrong key for example).

I've also tried to separate the Join part out of the loop which made it even worse and gave me 29mil records instead of 6700~. I feel I'm missing something very basic here.

Coming from an SQL back ground i'm finding it hard to do these joins without explicitly being able to tell Qlik Sense what fields to do the join on. I'm very new to Qlik so any advice is welcome.
I've attached my app below.

[Vegar]

I'll try check your attached app and come back to you tomorrow.

[spook2020202]

Thanks for your help. It's much appreciated.

[Vegar · Accepted solution]

You needed to add the leagueId into the common fields you use to do the join. 

You can considered leaving the tables as is without performing any joins .

[spook2020202]

I have much to learn. This works a treat and will serve as an example for future projects as well. Thanks for the help Vegar, much appreciated.

[Vegar]

I'm glad it worked out for you. and happy to be of help.

Good luck with this and your future projects.