Qlik Community

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?

Thanks for your help.

Mark

MVP
MVP

Re: Count of distinct serial numbers, some with extensions

using Marcus suggestion

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

Re: Count of distinct serial numbers, some with extensions

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

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

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

oknotsen
Honored Contributor III

Re: Count of distinct serial numbers, some with extensions

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!
Community Browser