Cobining data from a text file with ODBC data

[#1]

I do not have much experience with SQL statements but use the 'Load' statement and 'Left keep Load' commands in the script quite often.

Is it possible to combine data from a text file with a SQL database?

Load ID from C:\Test.csv;

with

ODBC CONNECT TO [DATABASE];

SQL SELECT ID, NAME

FROM DATABASE.PERSON;

How can I set up the script to collect the NAMEs from the DATABASE?

regards Einar

[johnw]

OK, so you want a table of IDs vs. Names, but ONLY for the IDs that are in the Test.csv file?

[My Table]:
LOAD ID FROM C:\Test.csv;
ODBC CONNECT TO [DATABASE];
LEFT JOIN ([My Table]) LOAD *;
SQL SELECT ID, NAME FROM DATABASE.PERSON;
DISCONNECT;

[#3]

Thank you. . That works, but a bit slow.

I hoped to access the DATABASE more directly with the SQL instead of scanning through the whole DATABASE with more than two million records to find the few IDs.

Is that possible ?

Thanks, Einar

[johnw · Accepted solution]

Yeah, that's possible, but a little more complicated, since you need to do it in SQL instead of in QlikView. You need to generate an in() statement from the list of IDs you loaded, then use that in the SQL. I'm not certain I have the syntax exactly right, but something like this:

[IDS1]:
LOAD ID FROM C:\Test.csv;
LET IDS = chr(39) & concat(ID,chr(39)&','&chr(39)) & chr(39);
DROP TABLE [IDS1];
ODBC CONNECT TO [DATABASE];
[My Table]:
LOAD *;
SQL SELECT ID, NAME
FROM DATABASE.PERSON
WHERE ID IN ($(IDS));
DISCONNECT;

Actually, I don't think you can use a concat() function in the script outside of a table load, though it's certainly what I'd try first. You might need to do something like this:

[IDS1]:
LOAD ID FROM C:\Test.csv;
[IDS2]:
LOAD chr(39) & concat(ID,chr(39)&','&chr(39)) & chr(39) as "ID List"
RESIDENT [IDS1];
LET IDS = peek('ID List');
DROP TABLES [IDS1],[IDS2];
ODBC CONNECT TO [DATABASE];
[My Table]:
LOAD *;
SQL SELECT ID, NAME
FROM DATABASE.PERSON
WHERE ID IN ($(IDS));
DISCONNECT;

[#5]

Thanks a lot John

The second example solved the problem

I used used the scipt and it collected my data in no time

[IDS1]:
LOAD concat(ID,chr(39)&','&chr(39)) as IDList FROM C:\Test.csv;
LET IDS = peek('IDList');
DROP TABLES [IDS1;
ODBC CONNECT TO [DATABASE];
SQL SELECT ID, NAME
FROM DATABASE.PERSON
WHERE ID IN ($(IDS));
DISCONNECT;