Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Andros, maybe:
=Count(DISTINCT Aggr(SubField(Reference, '-', 1), Reference))
count(DISTINCT SubField(Reference, '-'))
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#,'-'))
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.
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 |
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.
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.
load
distinct SubField(Reference, '-', 1) as Reference
Inline [
Reference
000123
000123-1
000123-2
00045679
40005679
40005679-1
40005679-2
];
Hi maxgo,
I got it, Appreciate your help
Andros Goh