Row Orderin Load script

[Peony]

Hi all

Please, help me to find a solution how to calculate row order for Action_ID field. Data should be aggregated by Customer and Date as it shows in sample table. What function to use in load script to get a result?

Customer_ID	Date	Action_ID	Action_Order
a1	25-Mar-2020	449e	1
a1	25-Mar-2020	4054rt	2
a1	8-Jul-2020	ac51-4bde	1
a1	8-Jan-2021	69600bc73fe4	1
a2	13-Jul-2020	a5fa6717f7bf	1
a2	14-Jul-2020	4828-a2c1	1
a2	14-Jul-2020	401r	2
a2	14-Jul-2020	c767	3
a2	10-Oct-2020	4a43-85de	1
a2	10-Oct-2020	45ae-ad4	2
a2	1-Feb-2021	2985-44f9	1

[joseph_morales · Accepted solution]

Hi @Peony 

You can try this:

AutoNumber(Action_ID,Customer_ID&'-'&Date)

In my example I use the DATE # to transform the Date field because I am loading the data from an inline, but in your case it would not be necessary.

LOAD 
Customer_ID,
DATE#([Date],'DD-MM-YYYY')		as Date,
Action_ID,
AutoNumber(Action_ID,Customer_ID&'-'&DATE#([Date],'DD-MM-YYYY'))  as Action_Order;
LOAD * Inline [
Customer_ID, Date, Action_ID
a1,	25-Mar-2020, 449e
a1,	25-Mar-2020, 4054rt
a1,	8-Jul-2020, ac51-4bde
a1,	8-Jan-2021, 69600bc73fe4
a2,	13-Jul-2020, a5fa6717f7bf
a2,	14-Jul-2020, 4828-a2c1
a2,	14-Jul-2020, 401r
a2,	14-Jul-2020, c767
a2,	10-Oct-2020, 4a43-85de
a2,	10-Oct-2020, 45ae-ad4
a2,	1-Feb-2021, 2985-44f9
];

Result

[Peony]

@joseph_morales  thank you much for your solution. It works perfectly for me.