topic Re: Identify and flag the repetitive daily-ordering pattern in QlikView

Identify and flag the repetitive daily-ordering pattern

ajipaul03 — Sat, 16 Nov 2024 02:39:37 GMT

Hello Experts,

I have a complex requirement to identify and flag the repetitive daily-ordering pattern.

Please see the sample data below.

The intend is to create the following fields as show in the table below

REPEAT_BATCH_ID
REPEAT_SEQ
FIRST_REPEAT

Any thoughts?

Thanks,

Aji

REC_ID	CUST	PROD	DT	REPEAT_BATCH_ID	REPEAT_SEQ	FIRST_REPEAT
1	C1	ABC	6/01/2019
2	C1	HIJ	6/01/2019
3	C1	XYZ	1/01/2019	1	1	First
4	C1	XYZ	2/01/2019	1	2	Repeat
5	C1	LMN	4/01/2019
6	C1	XYZ	5/01/2019	2	1	First
7	C1	XYZ	6/01/2019	2	2	Repeat
8	C1	XYZ	7/01/2019	2	3	Repeat
9	C1	XYZ	8/01/2019	2	4	Repeat
10	C1	XYZ	18/01/2019
11	C2	ABC	8/01/2019	3	1	First
12	C2	ABC	9/01/2019	3	2	Repeat
13	C2	HIJ	1/01/2019
14	C2	HIJ	3/01/2019	4	1	First
15	C2	HIJ	4/01/2019	4	2	Repeat
16	C2	HIJ	12/02/2019
17	C2	XYZ	3/01/2019	5	1	First
18	C2	XYZ	4/01/2019	5	2	Repeat
19	C2	XYZ	5/01/2019	5	3	Repeat
20	C2	XYZ	12/02/2019

Re: Identify and flag the repetitive daily-ordering pattern

Arthur_Fong — Fri, 23 Aug 2019 03:22:36 GMT

There you go:

Is a combination of both front end and back end script to do this.

Do have a look on the logic to build this.

Thanks and regards,

Arthur Fong

Re: Identify and flag the repetitive daily-ordering pattern

ajipaul03 — Fri, 23 Aug 2019 05:05:29 GMT

Hi Arthur,

Brilliant!!!

Couple of minor changes I did
the actual REC_ID is alphanumeric. so created a new filed using rowno() .
Used autonumber with Repeat_Batch_ID to generate the REPEAT_SEQ.

Thanks for your help!
Aji