tables

[saikripa]

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

[justISO · Accepted solution]

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: