URL Pagination using Qlik Sense Web file connector

[#1]

I am trying to establish a connection to a website to pull in table data. The data spans several pages (displays 50 records at a time). The site uses a paramaterized URL so you can control the records displayed via the URL. Ideally I can have one web file connection that can loop through the pages itself. The only solution I have been able to come up with so far is to create a new web file connection for each parameterized URL, which is definitely not an elegant solution.

This is what the URL structure is like.

http://<URL>?yearId=2017&pageId=50

[marcos-paixao]

Dear Cameron,

I solved this using one Web File. Here's a sample you can try.

Best Regards,

Marcos

[#3]

Hi Marcos,

Thanks for the sample code. I managed to get it to pseudo-work. I had to setup a web file connector and use the Lib:// rather than http://. This method with your code modified to fit my scenario did have the data load 'paging' sort-of. With this in place it would loop through the FOR EACH function for all the parameters passed to the variable and bring in a table of data. However, it was not actually paging through the URLs. Instead I would continually load the first page and add that to the table. So what I ended up with was a table of the first 50 records repeated x times (x being the number of parameter passed to the variable in the FOR EACH clause).

To ensure the variable contained the correct value I added the variable to the table. The result showed that the FOR EACH clause was definitely looping through the parameters but for whatever reason the resultant URL would only load the first page over and over.

If I try to manually load the different pages (i.e. change the &pageid in a browser to 0, or 50, or 100) the browser the different pages. I find this quite strange.

Here is the code I am using (the text is pasted below if needed);

This is the URL in the [lib:// ] connection;

http://games.espn.com/fhl/playerrater?leagueId=27827&splitTypeId=0&seasonId=2017&startIndex=$(Pagina...

With this code what I end up with is a list of the first 50 players repeated 21 times. Any thoughts?

Note: I tried adding single quotes around the variable parameters and that did not make any difference.

//** Sample Code **//

FOR EACH Pagination IN 0,50,100,150,200,250,300,350,400,450,500,550,600,650,700,750,800,850,900,950,1000

PlayerRaterRaw:

LOAD

    PLAYERS as Rank,

    PLAYERS1 as Player,

    STATUS as Owner,

    "PLAYER RATER" as Goals,

    "PLAYER RATER1" as Assists,

    "PLAYER RATER2" as PlusMinus,

    "PLAYER RATER3" as PIM,

    "PLAYER RATER4" as SOG,

    "PLAYER RATER5" as HIT,

    "PLAYER RATER6" as BLK,

    "PLAYER RATER7" as STP,

    "PLAYER RATER8" as Wins,

    "PLAYER RATER9" as Saves,

    "PLAYER RATER10" as Shutouts,

    "PLAYER RATER11" as GAA,

    "PLAYER RATER12" as Save%,

    "PLAYER RATER14" as Rating,

    '2017' as Year,

    $(Pagination) AS PageNumber

FROM [lib://ESPN Player Rater - Paging]

(html, codepage is 1252, embedded labels, table is @1);

NEXT Pagination;

[marcos-paixao]

Nice to hear news from you. I have two small advices to you:

1. You can change the line FROM THIS:

FROM [lib://ESPN Player Rater - Paging]

TO THIS:

FROM [http://games.espn.com/fhl/playerrater?leagueId=27827&splitTypeId=0&seasonId=2017&startIndex=$(Pagina...)]

You don't need exactly a connection webfile created for retrieving data. You can do it directly.

2. You have to store each result set into a QVD file. Otherwise, only the last result will be all your data.

To store the results, you must create a Folder Connection

Your generated QVD may look like this:

Afterwards, you can load the data on your application using the QVD and the results will be like:

I've prepared an app for helping you with the functional code.

Please download it from:

ESPN Results

I hope this can help!

Best Regards,

Marcos Aurelio Paixao

Product Manager (Sao Paulo, Brazil)

http://br.linkedin.com/in/mpaixao1

Skype Me: marcospaixao

"Choose a job you love, and you will never have to work a day in your life."

(Confucius)

[#5]

Thank Marcos, I appreciate you taking the time to develop that sample app. I downloaded it and imported the qvf into our Qlik Sense instance. However, when I try to run 'Load Data' I get an error;

The following error occurred:

This statement only works with lib:// paths in this script mode

If I understand this correctly it is saying that I can't define a connection directly to a URL, but rather I must use the lib:// action. My only guess here is that the behaviour in Qlik Sense is more limited than in QlikView regarding data connections. Unless I am missing something else completely.

[marcos-paixao]

Hi Cameron,

this occurs for security reasons. You can store the qvd in a folder and create a folder connection for read it. Otherwise you can disable the Standard Mode, but don't forget about uncle Ben adive: "With great power comes great responsibility."

There's a link about standard mode.

Disabling standard mode ‒ Qlik Sense