Count

[#1]

When I do a count distinct, look below field reference where they are 000123 and 000123-1, but they are actually same Reference belong to a PO. how to do a count that ignore – sign ?

I tried Count(distinct primaryReference), but didn’t work.

  also tried below but failed.

COUNT(IF(Index([OMSPO#], '-')=0, OMSPO#)
COUNT({$<OMSPO#-={'-'}>}OMSPO#)

Reference

000123

000123-1

000123-2

00045679

40005679-1

40005679-2

[rubenmarin]

Hi Andros, maybe:

=Count(DISTINCT Aggr(SubField(Reference, '-', 1), Reference))

[maxgro]

count(DISTINCT SubField(Reference, '-'))

[#4]

Hi maxgo & Ruben,

The above 2 solutions gave me different results ? Thought the results are close. I confirm the result via a manual process. Any other idea ? The actual field name is OMSPO#. Thank you.

count(DISTINC SubField(OMSPO#,'-'))

[#5]

Hi,

I also need to display the record in a straight table, where the record exclude "-" sign, e.g. 0001234-4 should be excluded but 0001234 included. Thank you.

[effinty2112]

Hi Andros,

This:

=count(DISTINCT(SubField(Reference, '-',1)))

returns 3.

Use this dimension for your straight table:

=SubField(Reference, '-',1)	count(1)
	6
000123	3
00045679	1
40005679	2

[#7]

Hi Andrew,

Sorry the table should be like this :

Reference

000123

000123-1

000123-2

00045679

40005679

40005679-1

40005679-2

my expected result is : total 3 records (000123, 00045679, 40056790.

those records with '-' should be excluded.

Thank you.

[Gabriel]

Hi,

What you can do is to make a copy of the field and used PurChar to to clear out any unwanted sign(s) so the reference number is unique to count.

[maxgro]

load

distinct SubField(Reference, '-', 1) as Reference

Inline [

Reference

000123

000123-1

000123-2

00045679

40005679

40005679-1

40005679-2

];

[#10 · Accepted solution]

Hi maxgo,

I got it, Appreciate your help

Andros Goh