Skip to main content
Marching toward a simplified navigation! READ ON
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Count of distinct serial numbers, some with extensions

Hi All,

I need your help with an expression. Currently I am trying
get a count of distinct serial numbers, but some of my serial numbers are
listed as



So the expression sees these as 2 distinct serial numbers. But
I need these to be seen as the same SN. How can I get my expression to read these as 1
distinct serial number?

5 Replies

Try something like this:

count(distinct subfield(SN, '_', 1))

- Marcus

Not applicable

Hi Marcus,

Thanks for the reply.I am just a novice at Qlikview and not sure I understand.

This is the formula I am currently using to count distinct SN's.

count(distinct if([Pass Number]=1,[Serial Number]))

How would i apply your logic to that?

Thanks for your help.



using Marcus suggestion

=count(distinct  if([Pass Number]=1, SubField([Serial Number],'_', 1)) )

Master III
Master III

count(distinct subfield(number, '_', 1))

count(distinct TextBetween('_'&number&'_','_','_',1))

Please bigger chunk of your data if you need more accurate answer

Master III
Master III

Alternatively, you could choose to create an extra field in the datamodel, since this field apparently not really is the serial but has some extra information attached to it.

Create something like "left([Serial Number], 9) as SerialNumber" in the table in the load script and count distinct that instead.

If the extra information has value for your application you could store it in a separate field. You can than even decide to leave out the "EXPLODED" if all of them have that and leave the remaining value as a time stamp or turn the "EXPLODED" into a separate flag field if it can be multiple options and has added value.

May you live in interesting times!