autonumber for client id x order id

[andreip21]

Hello,

i have a huge database or customer id, order id and value of order.

i want to add a new column (the 4th one), like a flag, which counts the number of the order for each customer.

for ex:

if customer A has 2 orders (#145 and #267) and customer B has 3 orders (#189 #211 and #214), the final table will be this one:

customer	order id	flag order
A	145	1
A	267	2
B	211	2
B	214	3
B	189	1

this will work also as a dimension, if selecting 1, i will have all the first order for all customers.

thanks a lot,

Andrei

[mikaelsc]

sort your table per customer, and in descending order of orders (lol) 

then something like

FinalTable:

load

if(previous(customerID)<>customerID,1,peek(NumberOfOrders)+1) as NumberOfOrders,

*

resident YourSortedTable;

[mikaelsc]

ascending order of orders is fine 

[NitinK7]

hi,

Try the following

use autonumber

ABC:
LOAD customer,
[order id],
value,
AutoNumber([order id],customer) as flag
FROM
data.xlsx
(ooxml, embedded labels, table is Sheet1);

or 

AutoNumber(RowNo(),customer) as flag

[Vegar]

I notice you, @andreip21 , got two working solutions to your problem (if I and my two fellow community members understood your problem correctly). 

Even though both suggestions can give you your desired output i would assume that @mikaelsc  will probably perform much faster than the slick autonumber solution by @NitinK7 .

So my suggestion would be to try the peek()-solution first, but it is crucial that you get the sort order right before you apply any of the suggestions. 

-Vegar

[Brett_Bleess]

Lastly, if one of the posts got you what you needed, or they both worked, please be sure to return to your thread to close things out by using the Accept as Solution button on the post(s) that helped you with things, as this gives them credit for the help and lets the other Members know what worked, so please consider closing things out for us, or leave a new post if you have further questions.

Regards,
Brett