Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
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


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
marcus_sommer

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?

Thanks for your help.

Mark

maxgro
MVP
MVP

using Marcus suggestion

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

robert_mika
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

oknotsen
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!