topic Re: 2 tables from same web page in QlikView

2 tables from same web page

Mon, 07 Apr 2014 16:27:45 GMT

I am trying to load table data from a web page. It loads it in, but it comes up as two different tables in the File Wizard (@1 and @2). They are two different tables and I need them to be as such, but not sure how to load them separately as I am using the URL to load the data. I also need to just certain cells (not rows) in data transformation, but I cannot select specific ones. How do I get the tables to load separately and how do I delete the specifici cells with garbage data? Fyi, the garbage data is'Â'. I tried copying and pasting it into the field but is just doen's seem to work (I can get it to delete the rows associated with this character, but not the specific cells and I need to keep the rows as they have needed data). Below is the first table labeled as '@1'.

Â 5K 10K HMAR MARA

FEMALE 58% 57% 61% 43%

Â 34:58 1:04:49 2:19:48 4:14:38

Â 33.0 35.7 35.6 36.6

MALE 42% 43% 39% 57%

Â 28:53 56:05 2:01:37 4:16:24

Â 33.7 38.4 38.6 40.2

Re: 2 tables from same web page

Mon, 07 Apr 2014 16:35:50 GMT

Alexander,

You would need to make changes to your script, specifically including 2 separate load statements (one for each table) and leaving out the fields you don't want to load.

Paste in your load script if you need additional clarification.

Thanks,

Clark

Re: 2 tables from same web page

Mon, 07 Apr 2014 16:47:40 GMT

Clark,

Here is the URL: Running USA's Annual Marathon Report | Running USA

It has several tables.

Here is the script that gets generated:

[Gender and Age Group Breakdown],

     [Gender and Age Group Breakdown1],

     [Gender and Age Group Breakdown2],

     [Â ],

     [Â 1],

     [Â 2],

     [Â 3],

     [Â 4],

     [Â 5],

     [Â 6]

FROM

[http://www.runningusa.org/index.cfm?fuseaction=news.details&ArticleId=332&returnTo=annual-reports]

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

It shows that threre are about 8 tables (@1, @2, @3, etc...), but only pulls from @1 in the script. So, I am missing sevral tables and the data is not remotely formatted correctly.

Re: 2 tables from same web page

Mon, 07 Apr 2014 16:49:25 GMT

I guess I don't know how to transform the data without using the wizard. I presume that there is a way to code it in the script, but I just don't have that level of expertise.

Re: 2 tables from same web page

Mon, 07 Apr 2014 16:57:17 GMT

The wizard works great for getting some load statements in as a starting point, but changes to these pre-formed load statements are almost always necessary.

If you want to paste your current script in, perhaps we can help point you in the right direction.

Re: 2 tables from same web page

Mon, 07 Apr 2014 17:03:54 GMT

Here is the code:

There are 14 separate tables on this web page. If I could load them individually that would make things much easier. FYI, the wizard seems to know they are not just one as it lists them each as @1, @2, @3, etc. for each consecutive table.

Re: 2 tables from same web page

Mon, 07 Apr 2014 21:03:55 GMT

Alex,

The problem is more with the data than anything else. Although the page is divided into 14 HTML tables, each one of these divisions contains separate data types in what should be multiple tables. In a single column, for instance we have a combination of percentages, counts and times.

While it may be possible to transform this source data into something usable, it would take some effort and could be undone at any moment if this website changes their layout. I recommend copying each table you want to use into it's own tab in excel and using that as your data source.

Thanks,
Clark

Re: 2 tables from same web page

Mon, 07 Apr 2014 21:27:36 GMT

Thanks Clark. I figured how to get it into my QV doc, but realized the same basic thing you just described. I am simply trying to create some sample work to assist on getting a job...and as an experienced marathoner, I understand this data. Thanks for your help!

Alex

Sent from my iPhone