topic Re: Load values from Web using values from another table in QlikView

Load values from Web using values from another table

ciaran_mcgowan — Mon, 17 Aug 2015 12:49:51 GMT

Hi all,

I have an issue where I need to extract several pages of data from websites using values from another table in the URL.

In other words, the initial extracts contains a brief overview of data for each year so that looks like this:

For i=2014 to Year(Now())

DailyRecords:
LOAD ID,
     date,
     type,
     cat
FROM
[http://sharepointwebsite.net/database/dblist.php?Year=$(i)]
(html, codepage is 1252, embedded labels, table is @1);

NEXT i

Below is the load script for one day:

DailyRecordDetails:
LOAD *
FROM 
[http://sharepointwebsite.net/database/record.php?id=20150111-B2GT]
(html, codepage is 1252, embedded labels, table is @1);

However, the id at the end of the URL needs to change to look at the page relating to each ID from the DailyRecords table. Can this be done?

Re: Load values from Web using values from another table

jagan — Mon, 17 Aug 2015 13:45:29 GMT

Hi,

Check below link for sample code

Load all Excel files and all sheets in a folder

Regards,

Jagan.

Re: Load values from Web using values from another table

ciaran_mcgowan — Mon, 17 Aug 2015 13:47:28 GMT

Hi Jagan,

I can't view the link:

This group is a private group.

The moderator of this group must approve membership or invite you before you can view group content or participate.

Re: Load values from Web using values from another table

jagan — Mon, 17 Aug 2015 13:52:27 GMT

LET vFilePath = 'C:\';

FOR EACH file in FileList('$(vFilePath)\*.xlsx'); // Loops each excel file in the given Folder

//In order to get the file information from SQLtables command making use of the ODBC connection format

ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

SheetNames:

SQLtables; // Loads all sheet names in the Excel file.

DISCONNECT;

FOR index = 0 to NoOfRows('SheetNames')-1 // Loops for each sheet in the Excel file.

LET sheetName = PurgeChar(PurgeChar(Peek('TABLE_NAME', index, 'SheetNames'), Chr(39)), Chr(36));

TableName:

Load * ,

FileBaseName()as FIle,

FileDir() as Dir,

FileName() as File_Name,

'$(sheetName)' as Sheet_name

From $(file)(ooxml, embedded labels, table is [$(sheetName)]);

NEXT index

DROP TABLE SheetNames;

Re: Load values from Web using values from another table

jonathandienst — Mon, 17 Aug 2015 13:57:02 GMT

Like this:

For i=2014 to Year(Now())

DailyRecords:

LOAD ID,

date,

type,

cat

FROM [http://sharepointwebsite.net/database/dblist.php?Year=$(i)]

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

Next i

For j = 0 To NoOfRows('DailyRecords')

Let vID = Peek('ID', j, 'DailyRecords');

DailyRecordDetails:

LOAD *

FROM [http://sharepointwebsite.net/database/record.php?id=$(vID)]

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

Next j

Re: Load values from Web using values from another table

ciaran_mcgowan — Mon, 17 Aug 2015 14:04:39 GMT

Perfect! Thank you