Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Techsam
Contributor II
Contributor II

LOAD/SELECT With function "Invalid Expression"

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)

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

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

 

2 Solutions

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

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

 

View solution in original post

marcus_sommer
MVP & Luminary
MVP & Luminary

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

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

- Marcus

View solution in original post

3 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

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

 

Techsam
Contributor II
Contributor II
Author

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)

marcus_sommer
MVP & Luminary
MVP & Luminary

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

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

- Marcus