Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
amiroh81
Creator
Creator

query from excell

Hi

I try to write query in SQL and to add condition from XLS file.

for example :

select *

from id_table

where id in (load id from excell)

what is the right syntax?

thanks in advaned

10 Replies
Not applicable

Hi,

Try this:

MyTable:

LOAD

     A,

     B,

     C

FROM

<FilePath\File.xlsx>

(ooxml, no labels, table is Page1)

Where upper(B) = 'ADMIN';

Not applicable

You can't use select command for excel data[flat files].

Load *

resident id_table

where id exists(id,id)

its_anandrjs

No this way is not possible you have to use where exists

Example:-

ExcelTab:

LOAD * Inline [

ID,Amt

A,203

B,304

C,105  ];

Id_Table:

LOAD * Inline  [

ID,Value

A,20

B,30

D,45

E,50  ] Where Exists(ID,ID);

Drop table ExcelTab; //And do not forget to drop this table.

Regards

Anand

PrashantSangle

Hi,

Try like

1st load you excell data

then load data from SQL

Like

Load ID

from Excel

Left join

Load * from id_table;

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
simenkg
Specialist
Specialist

First load the ID's you want to load from the excel file.

ValidIDs

Load ID as ValidIDs from Excel.xls [...];

Then you load the SQL

Id_Table:

Load * where Exists(ValidIDs,ID);
SQL Select * from id_table;

Drop table ValidIDs;

amiroh81
Creator
Creator
Author

What I mean is that I want to query in SQL when filtering operations subject which comes from Excel file.

For example, a list of names existed in an Excel file and I would like to be shown to me only those names from the all names.

the problem with the max dreamer and bwisenoSimenKG Suggestion is that i need to run the all table and its very big.

thanks very much

its_anandrjs

For your example

ExcelTab:

Load ID From Excel Source;

MainTab:

select *

from id_table Where(ID,id);

Drop Table ExcelTab; //Do not forget to drop.

Regards

Anand

tombombadil
Contributor III
Contributor III

Hi,

if there are not many names/ids from the Excel file, you could generate a string to filter the values directly in the WHERE clause of your SQL statement.

Like:

// You need to generate the string dyanmically from the Excel data

SET $FilterString="'ABC','BCD','DEF'";

table:

LOAD *;

SQL SELECT *

FROM id_table

WHERE ID IN ($(FilterString));

anbu1984
Master III
Master III

ID:

LOad Concat(Chr(39) & ID & Chr(39),',') As ID;

LOAD ID

FROM

[File.xlsx]

Let vId=Peek('ID',0,'ID');

Sql Select * from Table where ID in $(vId);