Skip to main content
Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for 
Search instead for 
Did you mean: 
CharlesNaseh
Contributor II
Contributor II

SQL Query with Excel exceptions

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!

2 Solutions

Accepted Solutions
CharlesNaseh
Contributor II
Contributor II
Author

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!

😉

 

View solution in original post

CharlesNaseh
Contributor II
Contributor II
Author

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!!!

View solution in original post

5 Replies
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
Contributor II
Contributor II
Author

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:

clipboard_image_0.png

clipboard_image_2.png

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
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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!!!