Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
andreip21
Contributor III
Contributor III

first, second, third order flag ... for each customer

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:

 

CustomerOrder IDTimestamp orderFlag (new column)
A100..1
A220...2
B110...1
B140...2
B270...3
4 Replies
Arthur_Fong
Partner - Specialist III
Partner - Specialist III

There you go:

First sort your table by Customer and Timestamp.

Then, build this flag using autonumber() function:

clipboard_image_0.png

Refer qvw attached as reference.

Thanks and regards,

Arthur Fong

Vegar
MVP
MVP

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];

Brett_Bleess
Former Employee
Former Employee

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
kushagra_jain
Contributor II
Contributor II

Thank you for this code, it really helped me and eased my work. Also learned something new. 🙂