Combine data from multiple datasources

[#1]

I have two datasources, one is excel file(one small table) & the other is a SQL Server (millions of records)

I need to pull in data from the SQL server based on the values i extract from the Excel file.

I was thinking i will do the following:

1) Pull in the list of companies from the Excel file into a resident table first

2) write a Query in script tab to Load the data from SQLDatabase by passing the list of companies i get from the Excel file

I need help with syntax of how do i pass the list of companies in a SQL query ?

[Anonymous]

Assuming you have a common field, say CompanyID, in both Excel and database table, it may look like this:

Excel:
LOAD
 CompanyID,
 <whatever esle you need from Exccel>
FROM ...;
Data:
LEFT KEEP LOAD
 *
;
SQL SELECT
 CompanyID,
 ...
FROM <database.table>;

Or it could be

Data:
LOAD
 *
WHERE exists(CompanyID);
SQL SELECT
 CompanyID,
 ...
FROM <database.table>;

[johnw]

If your Excel data will restrict your main load to only a small portion of the available rows, and if your data is indexed to support it, you may want to throw out the bad data on the SQL Server side instead of on the QlikView side. That way, hopefully SQL Server won't waste time retrieving millions of rows of data you don't want. I don't guarantee I have exactly the right syntax, but something like this:

Excel:
LOAD
CompanyID,
<whatever else you need from Excel>
FROM ...
;

GoodIDs:
LOAD DISTINCT CompanyID
RESIDENT Excel
;
SET IDs = chr(39) & peek('GoodIDs',0,'ID') & chr(39);
FOR I = 1 TO noofrows('GoodIDs')-1
LET IDs = IDs & ',' & chr(39) & peek('GoodIDs',I,'ID') & chr(39);
NEXT I

DROP TABLE GoodIDs
;
Data:
LOAD
...
;
SQL SELECT
CompanyID,
...
WHERE CompanyID IN ($(IDs))
;

[johnw · Accepted solution]

By coincidence, I just needed to do this for the first time with some new stuff I'm working on today. Or maybe when you're carrying a hammer, everything looks like a nail. In any case I debugged the code above and used it in an application I'm developing, at least temporarily. Here's the actual, working code:

BadIDs:
LOAD DISTINCT "Change Control with Bad Update" as "ID"
RESIDENT [Change Control History]
;
LET IDs = '';
FOR I = 0 TO noofrows('BadIDs')-1
LET IDs = IDs & ',' & chr(39) & peek('ID',I,'BadIDs') & chr(39);
NEXT I
LET IDs = mid(IDs,2);

DROP TABLE BadIDs
;
[Bad Change Controls]:
LOAD *
,DEID as "Change Control with Bad Update"
;
SQL SELECT *
FROM SYSTLC.ATDE101R
WHERE DEID IN ($(IDs))
;

[#5]

Brilliant

[#6]

Hi,

I'm trying to do the same but with a resident table.

To use your example, it would be like:

[A_TABLE]:

LOAD col1, col2

resident A_RESIDENT_TABLE WHERE col1 IN ($(IDs))

I get a "Garbage after statement" error message

Any idea.

Thanks in advance

L.