topic Re: API - How to get the loop working? in QlikView

API - How to get the loop working?

thomasgrimsby — Fri, 17 Mar 2017 07:54:24 GMT

Hi!

I want to go on with a little hobby project in QV.

This is regarding English football and a game called fantasy premier league.

The API I want to work properly in QV is https://fantasy.premierleague.com/drf/element-summary/1.

I have the REST connector and have read in the above API.

BUT this is just information about Player 1 in the league (Goalkeeper at Arsenal), hence the 1 at the end of the API URI

So my real question is if somebody could help me out writing a proper loop to get all the player data in the league!

To be able to do this I need a working loop that goes from i = 1 to max(element_u0) somehow.....

element_u0 is the Player ID.

Someone who can help?

Re: API - How to get the loop working?

mfchmielowski — Fri, 17 Mar 2017 13:00:03 GMT

Hi.

On https://fantasy.premierleague.com/drf/bootstrap you have key named total_players (at this moment value is 4436132). I've found information that there'is more info on this url while you're logged in.

let max = 2;


for i=1 to '$(max)'


  let connectionString = 'Provider=QvRestConnector.exe;url=https://fantasy.premierleague.com/drf/element-summary/'&i&';timeout=30;method=GET;autoDetectResponseType=true;keyGenerationStrategy=0;useWindowsAuthentication=false;useCertificate=No;certificateStoreLocation=CurrentUser;certificateStoreName=My;PaginationType=None;XUserId=dWbIXfC;XPassword=WLGRQLA;';

  CUSTOM CONNECT TO '$(connectionString)';

  RestConnectorMasterTable:
  SQL SELECT 
  "__KEY_root",
  (SELECT "id" AS "id_u3",/*...*/"fixture" AS "fixture_u0","opponent_team" AS "opponent_team_u0","__FK_history" FROM "history" FK "__FK_history")
  FROM JSON (wrap on) "root" PK "__KEY_root";

  [history]:
  LOAD
  $(i) as urlPlayedId, [id_u3],[fixture_u0],[opponent_team_u0],[__FK_history] AS [__KEY_root]
  RESIDENT RestConnectorMasterTable
  WHERE NOT IsNull([__FK_history]);

  DROP TABLE RestConnectorMasterTable;

next i;

This sample works. I didn't check what will happend when user was removed and url will return 404 reply code

Re: API - How to get the loop working?

mfchmielowski — Fri, 17 Mar 2017 13:06:32 GMT

GitHub - bobbymond/FantasyPremierLeagueAPI: A C# API for accessing data for Fantasy Premier League

here you've info about api you're using. Maybe you'll find more interesting info there

Re: API - How to get the loop working?

thomasgrimsby — Fri, 17 Mar 2017 14:36:05 GMT

Thanks a lot Michal.

That loop works when we are sure about how many players are in the league system

But is it possible to return the max(element_u0) somehow so that the script automatically finds all the id's.

When I now put in max = 100 i will get the 100 first players but if I put in max = 1000 the script fails when it can't find so many players (It ends around 650).

This below script is working but it would be amazing to get a loop that stops properly when all records are found!

let max=100;
for i=1 to '$(max)'

let connectionString='Provider=QvRestConnector.exe;url= https://fantasy.premierleague.com/drf/element-summary/'&i&';
timeout=30;method=GET;autoDetectResponseType=true;keyGenerationStrategy=0;useWindowsAuthentication=false;useCertificate=No;certificateStoreLocation=CurrentUser;certificateStoreName=My;PaginationType=Custom;XUserId=KNLDCcB;XPassword=HeeeWYC;';

CUSTOM CONNECT TO '$(connectionString)';

Bla bla bla

next i;

Re: API - How to get the loop working?

Peter_Cammaert — Fri, 17 Mar 2017 16:10:26 GMT

You'll have to figure out where on this web site you can get hold of the maximum number of players available for extraction. Any idea?

Re: API - How to get the loop working?

mfchmielowski — Mon, 20 Mar 2017 05:18:39 GMT

Look here:

https://github.com/LazyTarget/FantasyPremierLeagueAPI/tree/master/src/Helpers/WebRetriever/Pages‌

You'll find here some endpoints of api you're using.

Try to fetch info from https://fantasy.premierleague.com/drf/elements You'll find there full players list with ids.

Re: API - How to get the loop working?

thomasgrimsby — Wed, 22 Mar 2017 09:01:05 GMT

Hi again!

Thanks a lot for your answers Michal!

It was very useful to look in https://fantasy.premierleague.com/drf/elements to find how many ID's to use as max.

For now max=653

Unfortunately I'm just a QlikView user and have not tried Java so how to exactly use a max(ID) insted of typing in the x=653 or 700 when that changes is still a little mystery but not a real problem.

Things are working thanks to you

Re: API - How to get the loop working?

marksouzacosta — Sat, 15 Jun 2024 22:10:45 GMT

As an addition to this topic, I'm excited to share my latest tutorial on integrating Asana with Qlik Cloud using REST APIs!

In this step-by-step guide, you'll learn:
- How to use Asana REST APIs with Postman
- Creating REST Data Connections in Qlik Cloud
- Extracting data from Asana and saving as Parquet files
- Enhancing connections with custom parameters and pagination

Check out the full video on YouTube:
Integrate Asana with Qlik Cloud Using REST APIs: A Step-by-Step Guide
https://youtu.be/lFwar30BNkQ?si=UZoM_x9L5amdJSnf

Regards,

Mark Costa