# Qlik Education Discussions

Discussion Board for collaboration related to Qlik Education.

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

 N10HPDMM8 N10HPDMM8_EXPLODED_20131007070719

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?

Tags (1)
5 Replies

## Re: Count of distinct serial numbers, some with extensions

Try something like this:

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

- Marcus

Not applicable

## Re: Count of distinct serial numbers, some with extensions

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?

Mark

MVP

## Re: Count of distinct serial numbers, some with extensions

using Marcus suggestion

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

MVP

## Re: Count of distinct serial numbers, some with extensions

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

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