Announcements
cancel
Showing results 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

 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?

5 Replies

Try something like this:

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

- Marcus

Not applicable
Author

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

using Marcus suggestion

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

Master III

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

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