topic first, second, third order flag ... for each customer in QlikView

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

andreip21 — Wed, 25 Nov 2020 16:16:04 GMT

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

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

Arthur_Fong — Thu, 26 Sep 2019 00:06:30 GMT

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

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

Vegar — Thu, 26 Sep 2019 04:47:56 GMT

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

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

Brett_Bleess — Mon, 07 Oct 2019 19:25:29 GMT

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

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

kushagra_jain — Wed, 08 Apr 2020 10:22:04 GMT

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