Count of distinct serial numbers, some with extensions

[#1]

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?

[marcus_sommer]

Try something like this:

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

- Marcus

[#3]

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]

using Marcus suggestion

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

[robert_mika]

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

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

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

[oknotsen]

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.