topic Re: Count in QlikView

Count

Wed, 16 Dec 2015 07:52:44 GMT

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

Re: Count

rubenmarin — Wed, 16 Dec 2015 10:23:45 GMT

Hi Andros, maybe:

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

Re: Count

maxgro — Wed, 16 Dec 2015 18:33:27 GMT

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

Re: Count

Thu, 17 Dec 2015 02:19:12 GMT

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

Re: Count

Thu, 17 Dec 2015 03:31:01 GMT

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.

Re: Count

effinty2112 — Thu, 17 Dec 2015 15:08:21 GMT

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

Re: Count

Fri, 18 Dec 2015 01:24:41 GMT

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.

Re: Count

Gabriel — Fri, 18 Dec 2015 11:59:35 GMT

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.

Re: Count

maxgro — Fri, 18 Dec 2015 12:13:34 GMT

load

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

Inline [

Reference

000123

000123-1

000123-2

00045679

40005679

40005679-1

40005679-2

];

Re: Count

Sun, 20 Dec 2015 04:07:46 GMT

Hi maxgo,

I got it, Appreciate your help

Andros Goh