topic Re: where clause in App Development

where clause

vladimirkup — Tue, 12 Jan 2021 08:57:53 GMT

Hello all,

i am new to QLIK Sense developing and looking for help.

how do i code in Qlik Sence the following simple SQL query :

SELECT * FROM Table1
WHERE date = (SELECT MAX(date) FROM Table1)

i tried to do it with variable, something like this

set param1 = (SELECT MAX(date) FROM Table1)

Table2:

load *

resident Table1

where date = $'param1'

but that don't work.

please help

Thanks in advance

Re: where clause

marcus_sommer — Tue, 12 Jan 2021 16:44:02 GMT

Try it with:

... where date = $(param1) ...

- Marcus

Re: where clause

vladimirkup — Wed, 13 Jan 2021 14:24:39 GMT

HI,

that didn't work. i think this line does not work:

set param1 = (SELECT MAX(date) resident Table1)

when i execute in debug mode i do not see the param1 result in the output section.

is it even possible to set a variable from a query ? or is it just a syntax issue ?

Re: where clause

marcus_sommer — Wed, 13 Jan 2021 15:01:06 GMT

My suggestion was aimed to assign a string to a variable. But now I have the impression that you want to execute a query and assign the query-result to the variable - that's not possible. To get this you need to something like the following:

t: sql select max(date) as t from table;

set param1 = peek('t', 0, 't');

Depending on the target-query you may also need some formatting with date() and setting the variable-call within quotes like: '$(param1)'.

- Marcus

Re: where clause

vladimirkup — Sun, 17 Jan 2021 06:11:00 GMT

Thanks for the solution !