Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ciaran_mcgowan
Partner - Creator III
Partner - Creator III

Load values from Web using values from another table

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?

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

5 Replies
jagan
Luminary Alumni
Luminary Alumni

Hi,

Check below link for sample code

Load all Excel files and all sheets in a folder

Regards,

Jagan.

ciaran_mcgowan
Partner - Creator III
Partner - Creator III
Author

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.

jagan
Luminary Alumni
Luminary Alumni

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;

NEXT

jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
ciaran_mcgowan
Partner - Creator III
Partner - Creator III
Author

Perfect! Thank you