topic Re: LOAD/SELECT With function "Invalid Expression" in QlikView

LOAD/SELECT With function "Invalid Expression"

Techsam — Thu, 08 Oct 2020 07:25:12 GMT

Hello,

Im trying to load two fields where one of them only should contain 1 value.

If i only load the one field with LastValue function it works, but i need to have it connected(dont know the term)

with the other table.

the provided code gives the resulting error

Invalid expression
SQL SELECT
"D47505_Datum",
"D47501_Artikelkod"
FROM [REDACTED].PUPRHIST where ("D47505_Datum"<20200807)

LOAD
LastValue("D47505_Datum") as minDate,
"D47501_Artikelkod" as KeyArtikel;

SQL SELECT
"D47505_Datum",
"D47501_Artikelkod"
FROM [REDACTED].PUPRHIST where ("D47505_Datum"<$(Test));

Re: LOAD/SELECT With function "Invalid Expression"

marcus_sommer — Thu, 08 Oct 2020 08:44:32 GMT

Lastvalue() is an aggregation and needs a group by if there are further fields involved, like:

table1: SQL SELECT
"D47505_Datum",
"D47501_Artikelkod"
FROM [REDACTED].PUPRHIST where ("D47505_Datum"<$(Test));

table2: LOAD
LastValue("D47505_Datum") as minDate,
KeyArtikel
resident table1 group by KeyArtikel; /* you may also need an order by ... */

- Marcus

Re: LOAD/SELECT With function "Invalid Expression"

Techsam — Thu, 08 Oct 2020 09:16:01 GMT

This worked, however not with the result i needed.
I Want the resulting field to only have 1 value. This got me multiple.

maybe you know of an easier way to accomplish what i need.
what i need is based on user input load or change a field so it results in 1 row/value.
(i used the Test variable as input)

Re: LOAD/SELECT With function "Invalid Expression"

marcus_sommer — Thu, 08 Oct 2020 14:18:28 GMT

It's not really clear for me what do you want to do - this ?

table2: LOAD min("D47505_Datum") as minDate resident table1;

- Marcus