Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
i have 3 series of data (Customer ID, Order ID and Timestamp- Order Time). i would like to add a new column with a numeric flag for each order and this flag to show if that order was the first one made for that specific customer or the second one or the third one etc.
for ex:
Customer | Order ID | Timestamp order | Flag (new column) |
A | 100 | .. | 1 |
A | 220 | ... | 2 |
B | 110 | ... | 1 |
B | 140 | ... | 2 |
B | 270 | ... | 3 |
There you go:
First sort your table by Customer and Timestamp.
Then, build this flag using autonumber() function:
Refer qvw attached as reference.
Thanks and regards,
Arthur Fong
You can use the autonumber function presented by @Arthur_Fong , but it will perform slowly on a large data set with many customers. The peek method described below will be a bit more complicated to write, but more effective to run.
Load
Customer ,
[Order ID],
[Timestamp order],
If(peek('Customer') =Customer , peek('CustOrderSeq')+1, 1) as CustOrderSeq
Resident Data
Order by Customer, [Timestamp order];
Andrei, did either Arthur's example or Vegar's post help you with a solution? If one or both of them helped, consider using the Accept as Solution button on the post(s) that did help. If you did something different, consider posting that and mark that post, and if you are still working on things, provide an update on where you stand.
Here is a Design Blog post that may provide another idea, it is not quite the same thing, date-based, but the concept is what I thought might help. I have a feeling Vegar's post is likely the most efficient way to do things in this use case...
https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130
Regards,
Brett
Thank you for this code, it really helped me and eased my work. Also learned something new. 🙂