topic Combine data from multiple datasources in QlikView

Combine data from multiple datasources

Thu, 29 Apr 2010 19:36:26 GMT

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 ?

Combine data from multiple datasources

Anonymous — Thu, 29 Apr 2010 19:53:43 GMT

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>;

Combine data from multiple datasources

johnw — Thu, 29 Apr 2010 20:26:52 GMT

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))
;

Combine data from multiple datasources

johnw — Thu, 29 Apr 2010 23:05:40 GMT

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))
;

Combine data from multiple datasources

Wed, 12 May 2010 21:21:49 GMT

Brilliant

Combine data from multiple datasources

Thu, 30 Jun 2011 16:34:13 GMT

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