topic Dynamic Web URL Data in QlikView

Dynamic Web URL Data

montubhardwaj — Sun, 09 Nov 2014 18:31:15 GMT

Hi All,

Hope everyone is good. My problem is simple one but I am missing some point here. I am trying to pull the data from URL and use that into QlikView. Let me explain you sample scenario.

Let’s say that we have a URL (given below) and I am trying to pull the data from it. I am only interested to pull data from table 19 as
shown below. But this table # in URL keeps changing e.g. tomorrow the same information may be available at table 22. So, Instead of hardcoding this table in my script, is there a way where QlikView will automatically determine the table number where fields F1, 1 Year, Current etc. exists? I only want fields F1, 1 Year, Current and rest can be deleted and I do not want then data model.

http://www.moneycontrol.com/india/stockpricequote/miscellaneous/thomascookindia/TCI

http://www.moneycontrol.com/india/stockpricequote/computerssoftware/tataconsultancyservices/TCS

FYI, I have around 10 URLs with same data structure but different table number where fields F1, 1 Year, Current etc. exists.

Regards,

Sharma

Re: Dynamic Web URL Data

MarcoWedel — Sun, 09 Nov 2014 23:12:20 GMT

Hi,

maybe you could load the column names of the different tables using a crosstable load and check, if the columns exist, that you are interested in:

FOR vTableNo = 1 to 27
  tabColumns:
  CrossTable (ColumnName, ColumnValue)
  LOAD $(vTableNo) as TableNo,
      *
  FROM [http://www.moneycontrol.com/india/stockpricequote/miscellaneous/thomascookindia/TCI]
  (html, codepage is 1252, embedded labels, table is @$(vTableNo))
  Where RecNo()=1;
NEXT;

DROP Fields ColumnValue;

You then would load only the relevant table(s).

In your case there are more than one tables containing the columns of you are looking for:

hope this helps

regards

Marco

Re: Dynamic Web URL Data

montubhardwaj — Mon, 10 Nov 2014 02:49:37 GMT

Hello Marco, I want to thank you for the efforts you have taken to help me out here. Thanks much.

But problem comes when I add more than one URL to your logic. There are many other table are getting created during load e.g. Table-1, table-2 etc. Below are the issues which I want to address:

I only want to know the tables number where 1^stoccurrence of F1 happens. If there are many F1 fields, just want to know the table # where F1 appears 1^st time
We have set the loop from 1 through 27. Some of URLs are having only 20 tables and hence loop is failing. I guess I need to find a way for more intelligent loop.
Once we are able to find the desired tables (as in condition 1 above), I want to loop through all URLs and pull the data only from these tables.

Having any ideas?

Regards,

Sharma

Re: Dynamic Web URL Data

montubhardwaj — Mon, 10 Nov 2014 20:41:40 GMT

Is there any possibility of using xml files from web url? will that be easier?