topic Re: Script use Excel List to SQL resquest in QlikView

Script use Excel List to SQL resquest

Thu, 27 Aug 2015 14:53:31 GMT

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.

Re: Script use Excel List to SQL resquest

Qrishna — Thu, 27 Aug 2015 14:56:13 GMT

Sorry.

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

Re: Script use Excel List to SQL resquest

Thu, 27 Aug 2015 15:12:14 GMT

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.

Re: Script use Excel List to SQL resquest

Qrishna — Thu, 27 Aug 2015 15:18:31 GMT

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;

Re: Script use Excel List to SQL resquest

Thu, 27 Aug 2015 15:38:52 GMT

Sorry,

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

Re: Script use Excel List to SQL resquest

Qrishna — Thu, 27 Aug 2015 15:45:50 GMT

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.

Re: Script use Excel List to SQL resquest

Thu, 27 Aug 2015 15:47:25 GMT

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

Re: Script use Excel List to SQL resquest

Thu, 27 Aug 2015 15:50:57 GMT

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)

Re: Script use Excel List to SQL resquest

Qrishna — Thu, 27 Aug 2015 15:53:31 GMT

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

Re: Script use Excel List to SQL resquest

Thu, 27 Aug 2015 18:49:56 GMT

Hi,

Thanks to your help.

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

Re: Script use Excel List to SQL resquest

maxgro — Thu, 27 Aug 2015 19:29:52 GMT

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

Re: Script use Excel List to SQL resquest

Fri, 28 Aug 2015 16:19:17 GMT

Hi,

Thanks Massimo, it's seem work perfectly.

Sorry Krishna, today i try directly Massimo solution.

Thanks to your help.

Simon