Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
ascending order of orders is fine
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
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
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