Loading data in Qlik Sense from SQL table based on an Excel file

[kasimyc]

Hello,

I'm trying to load data into Qlik Sense from a SQL table, but I want to only load the data for specific customers that are listed in an Excel file. Here's what I am trying to achieve:

1. I have a customer portfolio in Excel that contains around 100k customer data.
2. I have a revenue table in my SQL database that contains around 50 million records.
3. I want to load only the customers from the SQL table that exist in the Excel file.

Here's an example of what I've tried so far:

Customers:

LOAD

    CustomerID as MUSTERI_ID,  ManagerID, SpeacialistID

FROM [lib://MyExcelFile/Customers.xlsx]

(ooxml, embedded labels, table is Customers);

Revenue:

SQL SELECT

    ID as MUSTERI_ID,

    Revenue,

    RevenueDate

FROM RevenueTable

WHERE EXISTS(ID, MUSTERI_ID)

and RevenueDate = '30.06.2023'

and Financial_id='100'

and kpi_id in ('1001',1048');

I expect the `WHERE EXISTS(ID)` part in the SQL statement to only load records from the SQL table that match the customers listed in the Excel file. However, I'm not sure if I'm using the `EXISTS` function correctly in this context. 

Could you please provide some guidance on how I can achieve my goal? Is there a better way to approach this?

Some of my limitations are on the excel file: I cannot import the excel file into the database, I have to work from excel.

Thank you in advance for your help.

[BrunPierre]

Hi, @kasimyc In these cases, preloading is usually used.

Revenue:
LOAD ID as MUSTERI_ID,
Revenue,
RevenueDate
Where Exists(MUSTERI_ID,ID)
and
RevenueDate = '30.06.2023'
and
Financial_id = 100
and
Match(kpi_id,'1001',1048');
SQL SELECT "ID",
"Revenue",
"RevenueDate"
FROM dbo."SOURCETABLE";