Solved: Script use Excel List to SQL resquest - Qlik Community

Report Inappropriate Content · ‎2015-08-27

Hello,

I don't if it's possible...

I have an Excel file like this :

     Num_Customer
     0123
     5156
     9656

...

1. I want load this file in my script --> OK

(using : LOAD Num_Customer FROM [path:excel_file.xls] (biff, embedded labels, table is feuil1$);

2. Use it in SQL request like : --> KO

Name_TABLE : SELECT * FROM SQL_table_Name WHERE Customer IN ( MY_CUSTOMER_IN_EXCEL_LIST)

I know it's possible to use 'Entry field' (using : '0123', '5156','9656')

Somebody to help ?? Thanks.

maxgro · ‎2015-08-27

for 100 customers you can try to build a Qlik variable and use it in SQL statement

// replace woth your excel file

excel:

load * inline [

Num_Customer

0123

5156

9656

100

200

];

filter:

load

chr(39) & concat(Num_Customer, chr(39) & ',' & chr(39)) & chr(39) as f

Resident excel;

let vfilter = Peek('f');

drop table filter;

now use the variable vfilter

Name_TABLE:

SQL

SELECT * FROM SQL_table_Name WHERE Customer IN ($(vfilter));

View solution in original post

krishna_2644 · ‎2015-08-27

Sorry.

do you mean, you only want to load the values which exists in num_customer?

Report Inappropriate Content · ‎2015-08-27

Hi,

Yes, tables in my datawehouse have more than 100.000.000 lines, and users want just see around 100 customers.

I don't want load all lines from my database...
So, i have an excel file with customer (can change often)... and i want use this file to load only the good line.

krishna_2644 · ‎2015-08-27

oh got it. then try this:

CustomersFromExcel:

Load Disitinct

CustomerID,

OtherCustomerFieldList

from Customer.excel;

Data:

load *,CustID

from xyz

where exists(CustomerID,CustID);

drop table CustomersFromExcel;

Report Inappropriate Content · ‎2015-08-27

Sorry,

it seems not work, for information my database is on Sql Server

krishna_2644 · ‎2015-08-27

that was a script format .

You have to edit that according your connections and tables.

if you have no idea,please post the table names and their fields.

Report Inappropriate Content · ‎2015-08-27

I have an error : impossible open file 'c:\........\my_table' ...

Report Inappropriate Content · ‎2015-08-27

EXCEL_LIST :

LOAD TEXT(TOTO) as TOTO_1
FROM

(biff, embedded labels, table is feuil1$);

DATA :
load * from dwh.dbo.my_table where exists(TOTO_1,TOTO_ID)

krishna_2644 · ‎2015-08-27

try this way:

YourCustomerTable:

LOAD Num_Customer

FROM [path:excel_file.xls]

(biff, embedded labels, table is feuil1$;

Name_TABLE :

load *

where where exists(Num_Customer ,CustomerID);

SELECT *

FROM SQL_table_Name

WHERE Customer IN ( MY_CUSTOMER_IN_EXCEL_LIST); //this where clause is not mandatory//

drop table YourCustomerTable;

Chage the field names as per you table field name.

Thanks

Report Inappropriate Content · ‎2015-08-27

Hi,

Thanks to your help.

I'm at home, i hope find time to try tomorrow...