get value by max date in weeks

[bartwelvaarts]

hello friends of the qlik community,

I want to get the value of a particular order based on the max date, per week.

Now i only get the value of the max date, but i want this per week. This is how i do it now:

table1:
load
order,
amount,
datetime

from source

left join table1:
temo:
LOAD DISTINCT
Order
MAX(Datetime) AS MAX_MODIFIED
RESIDENT table1
GROUP BY order;

left JOIN(table1)
TEMP2:
LOAD DISTINCT
Order
amount
RESIDENT table1
WHERE Datetime=MAX_MODIFIED;

I added an example in excel with the desired output.

Hopefully someone can help me!

[justISO]

Hi, you can try something like this:

table1:
load
order,
amount,
datetime
from source;

JOIN (table1)
LOAD DISTINCT
order,
week(datetime) as week,
Timestamp(MAX(datetime)) AS datetime
RESIDENT table1
GROUP BY order, week(datetime);

table:
LOAD
project, order, amount, week
RESIDENT table1
WHERE not isnull(week);

drop table table1;