LOAD/SELECT With function "Invalid Expression"

[Techsam]

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

[marcus_sommer · Accepted solution]

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]

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 · Accepted solution]

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

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

- Marcus