Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Count

 

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

1 Solution

Accepted Solutions
Not applicable
Author

Hi maxgo,

I got it, Appreciate your help

Andros Goh

View solution in original post

9 Replies
rubenmarin1

Hi Andros, maybe:

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

maxgro
MVP
MVP

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

Not applicable
Author

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#,'-'))

Not applicable
Author

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
Master
Master

Hi Andros,

This:

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

returns 3.

Use this dimension for your straight table:

=SubField(Reference, '-',1) count(1)
6
0001233
000456791
400056792
Not applicable
Author

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
Partner - Specialist III
Partner - Specialist III

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
MVP
MVP

load

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

Inline [

Reference

000123

000123-1

000123-2

00045679

40005679

40005679-1

40005679-2

];

1.png

Not applicable
Author

Hi maxgo,

I got it, Appreciate your help

Andros Goh