Mark the latest sale for each customer

[Amit_B]

Hi,

I have a table that contains a sales list.
The relevant fields are SaleID, Date & CustomerNum.
I want to create a flag field that will mark the latest sale for each customer.

I done it by sorting the table and use Previous() function, but it causes long loading time (maybe because the table contains tens of millions of rows).
I'm looking for a smarter and simpler way to do it.

Thanks.

SaleID	Date	CustomerNum	flag
0001	1/1/24	A	0
0002	1/1/24	B	1
0003	2/1/24	C	0
0004	2/1/24	A	1
0005	3/1/24	C	1

[marksouzacosta · Accepted solution]

Hi @Amit_B,

What about this?

TempSales:
LOAD * INLINE [
SaleID, Date, CustomerNum
0001, 1/1/24, A
0002, 1/1/24, B
0003, 2/1/24, C
0004, 2/1/24, A
0005, 3/1/24, C
];

LEFT JOIN(TempSales)
LOAD
    CustomerNum,
    Max(Date) AS Date,
    1 AS LastSale
RESIDENT
	TempSales
GROUP BY
	CustomerNum
;

NoConcatenate

Sales:
LOAD
    SaleID, 
    Date, 
    CustomerNum, 
    Coalesce(LastSale,0) AS LastSale
RESIDENT
	TempSales
ORDER BY
	SaleID
;

DROP TABLE TempSales;

Regards,

Mark Costa

[anat]

You may also give a try by using firstsortedvalue function

[Amit_B]

Thanks!

[Phan_Anh_Son]

I am trying to find an expression that will allow me to calculate the Opening balance of an account by period. There are multiple accounts in the table but it worked only in second line.

please help me get the Opening  according to Account.

Thanks.

[marksouzacosta]

Hi @Phan_Anh_Son,

I'll be glad to help but can you please open another ticket for that and paste the link for the new ticket here?
This way your issue will be more visible to the whole community.

Can you please also add a few more details to your error description? Screen shots pointing the problems usually helps a lot.