exclude data from a query

[taz803]

I have a sql query that displays a table of names and other columns I want to load an excel file that contains the list of names to exclude.
exemple of query :

name | age | date 

david |26     | 1996

Alain | 29 | 1985

Pierre| 54 | 1977

exemple of excel file :

name | age | date 

david |26     | 1996

so the result will be :
sql query - excel file 

name | age | date 

Alain | 29 | 1985

Pierre| 54 | 1977

how can i do this

[snibrahim1993]

Hi @taz803 

Please try the below.

1. Load the SQL query result into Qlik Sense as a table using the SQL SELECT statement. Let's assume the table is named "OriginalData".

OriginalData:
LOAD
name,
age,
date FROM YourTable; -- Replace "YourTable" with the actual table name in your SQL database

2. Load the Excel file containing the list of names to exclude into Qlik Sense and store it in a separate table. Let's assume the table is named "ExclusionList".

ExclusionList:
LOAD name
FROM [path_to_your_excel_file.xlsx] (ooxml, embedded labels, table is Sheet1); -- Replace "path_to_your_excel_file.xlsx" with the actual file path and sheet name if applicable

3. Use the "Left Keep" function to exclude the names present in the "ExclusionList" from the "OriginalData" table and store the result in a new table called "FilteredData".

FilteredData:
Left Keep(OriginalData)
LOAD *
Resident ExclusionList;

The "Left Keep" function keeps only the records from the "OriginalData" table where the names exist in the "ExclusionList" table, effectively excluding the names present in the Excel file.

[taz803]

i tried you solution but i'm blocked here :

OriginalData:
LOAD
name,
age,
date FROM YourTable; -- Replace "YourTable" with the actual table name in your SQL database 

my request it contient 13 colonnes and its a join request from 3 tables 

[BrunPierre]

Hi @taz803, Something like this.

Excel:
LOAD Capitalize(Trim(Name)) as NamesToExclude,
Age,
Date
FROM XYZ;

SQL:
LOAD *
Where not Exists(NamesToExclude,Capitalize(Trim(Name)));

SQL SELECT
FROM dbo.ABC
"Name",
"Age",
"Date"
FROM dbo.ABC;

DROP Table Excel;

[snibrahim1993]

Hi @taz803 
Please try this 

ExcludeNames:
LOAD name
FROM [lib://PathToYourExcelFile/ExcludeNames.xlsx]
(ooxml, embedded labels, table is Sheet1);

SourceData:
LOAD
name,
age,
date

SQL SELECT
name,
age,
date
FROM YourDatabase.YourTable;

FinalData:
LOAD name, age, date
RESIDENT SourceData
LEFT JOIN (ExcludeNames)
LOAD name
RESIDENT ExcludeNames
WHERE IsNull(name);

DROP TABLE ExcludeNames;
DROP TABLE SourceData;