SQL Query with Excel exceptions

[CharlesNaseh]

Hi, everyone!

I need to make a select in a SQL table excluding the codes that are in an already loaded Excel.
Example:

Excel (lonely column)
Code
1
4
6
9
10
67 (... almost 400 codes)

SQL Server table
Code - Name
1 - Jose
1 - Maria
3 - Maria
3 - Carlos
8 - Joao
10 - Joao
10 - Pedro
65 - Carlos
67 - Carlos (... a lot of lines)

I need something like "Select * From SQL_Table Where Code Not Exists In Excel_Table".

I'm migrating an application that was in PowerBI originally, so I'm not very close to the QlikView scripting functions.

Thaks a lot!

[Nicole-Smith]

Something like this should do it:

ExcludeCodeList:
LOAD Code
FROM YourExcelFile.xlsx;

CodeFinal:
LOAD * WHERE NOT EXISTS(Code);
SELECT Code, Name
FROM YourSQLTable;

DROP TABLE ExcludeCodeList;

[CharlesNaseh]

Hi, Nicole.

Thank you for your answer. The result was something weird - only a low percent of lines should be excluded, but the final result was too low. I'll analyze the tables. May I ask you just one more question?

I have this case:

I need to make a third grid = (first grid - second grid), where date = today and group by each letter  (that is a group of specified cars - the first grid is all my cars available and the second grid the cars picked by hour today and tomorrow). I need to see if I'll have enough cars today. My set analysis filters are not working. The third grid must be like this:
AM - 143 (492 from the first table minus 349 from the second)
AT - 54 (87 - 33)
B - 1089 (...)
and so on...

Each grid was filled from two distincts Selects in the script.

May you give me some idea?

Thanks again! 😉

[Nicole-Smith]

Can you post a sample file?  Hard to tell what you need to do without seeing data and data model.

[CharlesNaseh · Accepted solution]

Hi, again, Nicole!

I solved this problem filtering date with set analysis and creating a syn key with "group" and other fields I needed.
Sorry for the poor information before, but thank you so much for all your attention!

😉

[CharlesNaseh · Accepted solution]

Oh, and about the first problem, I solved using different names for each field, like this:

load * where not exists (filter_field, filtered_field);

Your help was essential!!!