Flag If Missing Values

[its_rajvir]

Dear Experts,

I have some data shown below in the table. For each Txn Equip should maintain a seq no. from 1 to 10 and again reset to 1 after 10th txn on the equip. Here i want to know which are the Equip which reset without completing the seq of 1 to 10. In example the output should be 54657.

Equip	Seq No
39201	1
39201	2
39201	3
39201	4
39201	5
39201	6
39201	7
39201	8
39201	9
39201	10
54657	1
54657	2
54657	3
54657	4
54657	5
54657	6
54657	1
64567	1
64567	2
64567	3
64567	4
64567	5
64567	6
64567	7
64567	8
64567	9
64567	10

Please Help!

[tresesco]

Rajvir, where do you want this? In a chart? KPI?

[its_rajvir]

Dear @tresesco I want to show the Equip (ID). with missing sequence in the straight table. or you can suggest how i can do this i just want to know those Equip (IDs).

[its_rajvir]

Dear @tresesco its an urgent request 😑, it would be so helpful if you will answer.

[tresesco · Accepted solution]

If(Count(DISTINCT [Seq No])=10 and Max([Seq No])=10, 'OK', 'Seq Missing')

I know qlik community is a place where you get almost every query resolved very fast, still - since it's a free of charge service - people who are trying to help you might not be in same urgency to help; they will come here and try to help in their free time. So my suggestion would be - to not depend on community for urgent issues.

[its_rajvir]

@tresesco Thankyou for your time and efforts.

[tresesco]

I was trying to work on an expression without if, and here you go:

Coalesce(EmptyIsNull(Repeat('OK',(Count(DISTINCT [Seq No])=10 and Max([Seq No])=10)*-1)),'Seq Missing')

of course iff, you like complexity to avoid complexity and an if. 😋