topic How to Load data from web when data table is at multiple pages in QlikView

How to Load data from web when data table is at multiple pages

shekhar_analyti — Wed, 25 Nov 2020 16:16:04 GMT

Hi All ,

How to load Election Result data from a website when the data table is scattered at multiple pages .

Website : http://eciresults.nic.in/StatewiseS24.htm

Data pattern

My requirement is that my qvw app should load data one by one from the above web data table till it reaches

end . Here its 41th page .

Thanks

Re: How to Load data from web when data table is at multiple pages

hector_munoz — Sun, 12 Mar 2017 15:36:04 GMT

Hi Shekhar,

In that web you have a URL parameter that identifies which partition of table shows:

1 -> http://eciresults.nic.in/StatewiseS24.htm

2 -> http://eciresults.nic.in/StatewiseS241.htm

3 -> http://eciresults.nic.in/StatewiseS242.htm

4 -> http://eciresults.nic.in/StatewiseS243.htm

5 -> http://eciresults.nic.in/StatewiseS244.htm

....

40 -> http://eciresults.nic.in/StatewiseS2439.htm

41 -> http://eciresults.nic.in/StatewiseS2440.htm

So, we could consider a rule like:

IF page = 1 THEN 24 ELSE (240 + page - 1)

, and create a for loop like:

FOR vsCounter = 1 TO 41

LET vsPage = If($(vsCounter) = 1, 24, 240 + $(vsCounter) - 1);

LET vsURL = 'http://eciresults.nic.in/StatewiseS' & '$(vsPage)' & '.htm';

LOAD @1 AS Constituency,

@2 AS [Const. No.],

@3 AS [Leading Candidate],

@4 AS [Leading Party],

@5 AS [Trailing Candidate],

@6 AS [Trailing Party],

@7 AS Margin,

@8 AS Status

FROM $(vsURL) (html, codepage is 1252, no labels, table is @7)

WHERE Recno() > 4;

I think this should serve to you...

Regards,
H

Re: How to Load data from web when data table is at multiple pages

shekhar_analyti — Mon, 13 Mar 2017 07:59:04 GMT

Hi Hector ,

Thanks for detailed information .

It will be great if you can help me with the approach of one more scenarios related with above :

1) If the count of number pages changes daily . For example , current day its

41 -> http://eciresults.nic.in/StatewiseS2440.htm , but next day it increases by two to become 43 (http://eciresults.nic.in/StatewiseS2442.htm) , and next day increase by 5 to become 48 (http://eciresults.nic.in/StatewiseS2447.htm )

Thanks & Regards

Shekhar

Re: How to Load data from web when data table is at multiple pages

hector_munoz — Mon, 13 Mar 2017 09:20:20 GMT

Hi Shekthar,

You can use a trick that consists in read e.g. 100 pages and control reload breaks with ErrorMode variable:

FOR vsCounter = 1 TO 100

LET vsPage = If($(vsCounter) = 1, 24, 240 + $(vsCounter) - 1);

LET vsURL = 'http://eciresults.nic.in/StatewiseS' & '$(vsPage)' & '.htm';

SET ErrorMode = 0;

LOAD @1 AS Constituency,

@2 AS [Const. No.],

@3 AS [Leading Candidate],

@4 AS [Leading Party],

@5 AS [Trailing Candidate],

@6 AS [Trailing Party],

@7 AS Margin,

@8 AS Status

FROM $(vsURL) (html, codepage is 1252, no labels, table is @7)

WHERE Recno() > 4;

SET ErrorMode = 1;

In the above example we try to read 100 pages. We disable reload break with ErrorMode = 0 begore we try to read the page of the iteration. After reading or trying to read it, web enable reload break again (ErrorMode = 1).

Can you try it?

Regards,
H