QLIK Sense REST request

Question

I made a REST connection in Qlik Sense using the URL below, but the URL I am using will only return data for player ID of 237. I would like to return all data under the Player library instead and not for one specific ID.

http://api.football-api.com/2.0/player/237?Authorization=0123456789

I've tried using an asterisk(*) instead of the 237 and it didn't work, I'm wondering if the Query Parameters or Query Headers fields might be the solution. All of the examples I've seen to fill the REST connection form have been simple and not helpful in this situation.

Answer 1

You can use variable instead of number.

I can't view data because I don't have authorization, Need to know the total number of playerID in URL.

// Variable setting, Total Row of PlayerID's Number.
let Id = FieldValue('playerId', 1);
//or
let Id = total number; 


// Loop Start
for start = 1 to $(ID)


// exampleQuery
RestConnectorMasterTable:
SQL SELECT
    "__KEY_...."
    (SELECT ....
     FROM ...)
    FROM XML ...
WITH CONNECTION(Url "http://api.football-api.com/2.0/player/$(start)?Authorization=0123456789");

/* put a variable instead of ID's number in the WITH CONNECTION statement. */


// Loop End
Next start;


Play:
Load *
Resident RestConnectorMasterTable
Where Not IsNull([__FK_...]);


Drop table RestConnectorMasterTable;

I hope you find it helpful :)

---- Additional ----

I checked the api.

Simply running a loop from 1 to number will result in a 404 error. (Non exisetent parametes)

Do you need a player_id on the 9002 team?
If so the player_id of 9002 team can be found squad under root.
(rest connect → Select data to load → root > squad)

Therefore, you can get the id field of the squad table first, then loop on id.

step 1)
Connect the Rest Connector to the team API.
http://api.football-api.com/2.0/team/9002?Authorization=1234567890

team's parameter is 9002.

step 2)
Click on Select data to load to select the squad data under root.
And only the id field into the squad table. (total 28 rows)
enter image description here

stpe 3)
Set the pagination type to Custom in the existing player_id RestConnector option.
(WITH CONNECTION STATEMENT is created.)
enter image description here

step 4)
See the script below And Execute Load.

// Team API Connect
LIB CONNECT TO 'Football_Team';

RestConnectorMasterTable:
SQL SELECT 
    "__KEY_root",
    (SELECT 
        "id",
        "name",
        "__FK_squad"
    FROM "squad" FK "__FK_squad")
FROM JSON (wrap on) "root" PK "__KEY_root";

[squad]:
LOAD    [id] AS [id],
        [name] AS [name]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_squad]);


DROP TABLE RestConnectorMasterTable;



// Player_Id API Connect
LIB CONNECT TO 'Football_playerId';

LET total = NoOfRows('squad');        // Number of total.

For i = 0 to $(total) - 1
LET vID = Peek('id', $(i), 'squad');  // Loop start id by 9002 team.


// The following default script was commented out unnecessarily.
// Action required: Implement the logic to retrieve the total records from the REST source and assign to the 'total' local variable.
// Let total = 0;
// Let totalfetched = 0;
// Let startAt = 0;
// Let pageSize = 100;
// for startAt = 0 to total step pageSize

RestConnectorMasterTable:
SQL SELECT 
    "id" AS "id_u3",
    "common_name",
    "name" AS "name_u3",
    "firstname",
    "lastname",
    "team",
    "teamid",
    "nationality",
    "birthdate",
    "age",
    "birthcountry",
    "birthplace",
    "position",
    "height",
    "weight",
    "__KEY_root"
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION(Url "http://api.football-api.com/2.0/player/$(vID)?Authorization=0123456789");
// player/id? → Change the ID to a variable. *****
// Action required: change URL included in 'WITH CONNECTION' as needed to support pagination for the REST source. 
// Please see the documentation for "Loading paged data."
// NEXT startAt;


[Player]:
LOAD    [id_u3] AS [id_u3],
        [common_name] AS [common_name],
        [name_u3] AS [name_u3],
        [firstname] AS [firstname],
        [lastname] AS [lastname],
        [team] AS [team],
        [teamid] AS [teamid],
        [nationality] AS [nationality],
        [birthdate] AS [birthdate],
        [age] AS [age],
        [birthcountry] AS [birthcountry],
        [birthplace] AS [birthplace],
        [position] AS [position],
        [height] AS [height],
        [weight] AS [weight]
    //  , [__KEY_root] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_root]);

DROP TABLE RestConnectorMasterTable;

Next i;  // Loop End.


DROP TABLE squad;  // id info table delete.

Hear are the results:
enter image description here

Hope you get the results you want :D

Answer 2

Documetation: https://football-api.com/documentation2/#!/Player/get_player_player_id

say that player_id is required and you can not download all using *.

I think way to bypass it is asking about player by player in chunks like it using loop:

FOR i=1 to 1000

    // your code here just get player using $(i) as id
    // you can save also player data as qvd so you will not ask another time for that
    // (most api have plimitation with number of calls)

NEXT i

Answer 3

You can't replace player with * and expect to get all data. This is not how REST APIs are working. Ideally you should have players (for example) end point which should return list with all players (including they id) and then loop through all (or subset) of them to get more details. As far as i can see there is no such endpoint in football-api.com api documentation. Probably is better to contact they support team, if they can provide you with such information

In Qlik this solution might looks like this:

// Lets imagine that this table is the API response that
// returns list with all players
Players:
Load * Inline [
  playerId
  1
  2
  3
  4
  5
]; 

// Loop through all values in playerId field
for i = 0 to FieldValueCount('playerId')

  // vPlayerId variable will hold the current iteration player id
  let vPlayerId = FieldValue('playerId', $(i))

  // Load player data. The player id variable is in the url
  Details:
  Load
    *
  From
    http://api.football-api.com/2.0/$(vPlayerId)/237?Authorization=0123456789
  ;

next

// We can drop the Players table if not needed anymore
Drop Table Players;

BTW Try and not put such things as Authorization keys over the internet.