topic Re: query from excell in QlikView

query from excell

amiroh81 — Wed, 10 Dec 2014 11:31:45 GMT

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

Re: query from excell

Wed, 10 Dec 2014 11:41:09 GMT

Hi,

Try this:

MyTable:

LOAD

FROM

<FilePath\File.xlsx>

(ooxml, no labels, table is Page1)

Where upper(B) = 'ADMIN';

Re: query from excell

Wed, 10 Dec 2014 11:46:26 GMT

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

Load *

resident id_table

where id exists(id,id)

Re: query from excell

its_anandrjs — Wed, 10 Dec 2014 11:49:20 GMT

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

Re: query from excell

PrashantSangle — Wed, 10 Dec 2014 11:53:33 GMT

Hi,

Try like

1st load you excell data

then load data from SQL

Load ID

from Excel

Left join

Load * from id_table;

Regards

Re: query from excell

simenkg — Wed, 10 Dec 2014 11:54:51 GMT

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;

Re: query from excell

amiroh81 — Wed, 10 Dec 2014 11:58:57 GMT

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

Re: query from excell

its_anandrjs — Wed, 10 Dec 2014 12:02:30 GMT

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

Re: query from excell

tombombadil — Wed, 10 Dec 2014 12:18:45 GMT

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));

Re: query from excell

anbu1984 — Wed, 10 Dec 2014 12:22:38 GMT

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);

Re: query from excell

amiroh81 — Thu, 11 Dec 2014 07:59:51 GMT

thanks a lot.

i need to add Brackets on :

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

thanks!