topic Re: Count duplicates and calculate with them in App Development

Count duplicates and calculate with them

hendriks_steffe — Wed, 15 Jun 2016 07:30:56 GMT

Hi,

I have a table which consists of the following data, for example:

Item No	Attribute A	Attribute B	Counted duplicates
1234	w	a	0,25
1234	x	b	0,25
1234	y	c	0,25
1234	z	d	0,25
2345	f	e	1
3456	g	t	0,33
3456	h	n	0,33
3456	i	m	0,33

...

I need to create that last column. To do that I need something like:

1 / (how many times does an Item No exist in the table)

My approaches until now are:

1/count([Item No])

1/count(if([Item No]=[Item No], [Item No]))

1/(count(distinct [Item No])/count([Item No]))

(count(distinct [Item No])/count([Item No]))

But none of them produces the results I need.

Any help would be greatly appreciated.

Re: Count duplicates and calculate with them

Anonymous — Wed, 15 Jun 2016 09:14:45 GMT

Hi,

Is the attached qvw what you need ?

Regards,

Filip

Re: Count duplicates and calculate with them

sunny_talwar — Wed, 15 Jun 2016 10:28:25 GMT

Try this:

1/Count(TOTAL [Item No])

1/Count(TOTAL<[Item No]> [Item No])

Re: Count duplicates and calculate with them

hendriks_steffe — Wed, 15 Jun 2016 10:53:16 GMT

That would help. How did you create the last column in the loading script?

Re: Count duplicates and calculate with them

hendriks_steffe — Wed, 15 Jun 2016 11:06:53 GMT

Hi,

that does the same thing as 1/count([Item No]).

Re: Count duplicates and calculate with them

sunny_talwar — Wed, 15 Jun 2016 13:29:32 GMT

Isn't this what you wanted?

Re: Count duplicates and calculate with them

hendriks_steffe — Wed, 15 Jun 2016 13:47:09 GMT

Hi Sunny T,

Sorry but it seems I haven't made myself fully clear.

Your solution works just fine in a created Table as an element in Qlik Sense.

But I need this column in my original data, therefore I need a solution for the loading script.

If I try your approach in my loading script like this

,1/Count(TOTAL<[Item No]> [Item No]) as [Item_Counter];

I get the following error message:

Error in expression: ')' expected

Re: Count duplicates and calculate with them

sunny_talwar — Wed, 15 Jun 2016 15:17:09 GMT

For script based solution, try this out:

Table:

LOAD [Item No],

[Attribute A],

[Attribute B]

FROM

[https://community.qlik.com/thread/220880]

(html, codepage is 1252, embedded labels, table is @1);

Left Join(Table)

LOAD [Item No],

Count([Item No]) as Total

Resident Table

Group By [Item No];

FinalTable:

LOAD *,

1/Total as NewColumn

Resident Table;

DROP Table Table;

Re: Count duplicates and calculate with them

hendriks_steffe — Thu, 16 Jun 2016 07:27:34 GMT

This works just fine. Many thanks!