topic Re: Loading a Variable with an integer in QlikView

Loading a Variable with an integer

Tue, 21 Jan 2014 15:39:48 GMT

Hi all

In my script I am loading a table 'tblSubList' but I am not interested in every record in this table, and loading every record adds time to the script.

Earlier in the script I have another table 'tblCHI' that contains the ID numbers for the records I am interested in.

Can I take the minimum ID from 'tblCHI' and use this in the WHERE clause for 'tblSubList'? Or would a Left Join be just as efficient (as there may not be matching records in 'tblSubList').

In SQL Server I would do something like

@MinSysID = SELECT Min(SYS_ID) FROM tblCHI

SELECT * FROM tblSubList WHERE SYS_ID >= @MinSysID

Can a similar thing be done in QlikView?

Re: Loading a Variable with an integer

Clever_Anjos — Tue, 21 Jan 2014 15:44:53 GMT

Tmp:

SQL SELECT Min(SYS_ID) as min FROM tblCHI;

Let vMin=peek('min');

drop table Tmp;

SQL SELECT * FROM tblSubList WHERE SYS_ID >= $(vMin)

Re: Loading a Variable with an integer

alexandros17 — Tue, 21 Jan 2014 15:45:51 GMT

Yes, use the keyword EXISTS, in this way you can load only existing IDs previously loaded

Hope it helps

Re: Loading a Variable with an integer

Nicole-Smith — Tue, 21 Jan 2014 15:47:48 GMT

For tblSubList, you should be able to use a WHERE clause like:

WHERE exists(IDFromtblCHI, IDFromtblSubList)

Re: Loading a Variable with an integer

richard — Tue, 21 Jan 2014 15:48:02 GMT

Hello James,

The Qlikview equivalent way to approach this is by using the PEEK function.

E.g. peek(fieldname [ , row [ , tablename ] ] )

As in SQL you can put the result in a variable and the use that variable in the where clause of the tblSubList table.