Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I need to display a table which shows customer ID and latest order date. I just need to display these both columns and only the latest date should be there and not all the dates.
How do I write the expression of the date column.
table looks something like this:
AA 1/4/2022
AA 2/4/2022
BB 3/4/2022
BB 1/4/2022
CC 6/4/2022
I want it to look like this :
AA 2/4/2022
BB 3/4/2022
CC 6/4/2022
Hi, there are 2 ways to do that. First is in your load script, just extract max date for each customer. Example of code:
temp:
load * inline [
customer, orderdate,
AA , 2022-04-01
AA , 2022-04-02
BB , 2022-04-03
BB , 2022-04-01
CC , 2022-04-06
];
main:
load customer,
max(date(orderdate)) as MaxOrderDate
RESIDENT temp
group by customer;
drop table temp;
Other way, much simpler, to calculate that info in report level. Just create a simple table, with customerId as dimension, and max measure for date, like:
Hi, there are 2 ways to do that. First is in your load script, just extract max date for each customer. Example of code:
temp:
load * inline [
customer, orderdate,
AA , 2022-04-01
AA , 2022-04-02
BB , 2022-04-03
BB , 2022-04-01
CC , 2022-04-06
];
main:
load customer,
max(date(orderdate)) as MaxOrderDate
RESIDENT temp
group by customer;
drop table temp;
Other way, much simpler, to calculate that info in report level. Just create a simple table, with customerId as dimension, and max measure for date, like: