Set-variabele in select statement

[#1]

I want to have the result of the max(order_date) to put in a variabele so I can use it in the next Load in the where statement.

Example:

var_tabel:

Load
Date(MAX(ORDER_DATE),'YYYY-MM-DD') as selection_date

FROM c:\tmp\omzet1.qvd (qvd);
var_Seldate = selection_date;

`STORE_ID`,
`ORDER_DATE`,
AMOUNT;
SQL SELECT *
FROM`PCW_SALES`
where ORDER_DATE >= $(varSeldate);

[disqr_rm]

Try this:

AllDate:
Load DISTINCT ORDER_DATE FROM c:\tmp\omzet1.qvd (qvd);
MinMax:

Load Date(MAX(ORDER_DATE),'YYYY-MM-DD') as MaxDate RESIDENT AllDate
 GROUP BY 1; //Dummy grouping
LET var_Seldate = peek('MaxDate');
DROP Tables AllDate, MinMax;
LOAD
STORE_ID, ORDER_DATE, AMOUNT;
SQL SELECT * FROM`PCW_SALES`
where ORDER_DATE >= $(varSeldate);

You may have to play with date format and/or putting single quotes before and end of the variable. Like: where ORDER_DATE >= '$(varSeldate)';

If you debug the code, you will get the idea.

Hope this helps.

[#3]

Thanks I can live with it

[#4]

Are both queries from the same database? In which case you could use a subquery:

SQL SELECT *
FROM`PCW_SALES`
where ORDER_DATE >= (select max(order_date) from <table_name>)

[#5]

Yes

I got already the solution

Thanks