Count duplicates and calculate with them

[hendriks_steffe]

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.

[Anonymous]

Hi,

Is the attached qvw what you need ?

Regards,

Filip

[sunny_talwar]

Try this:

1/Count(TOTAL [Item No])

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

[hendriks_steffe]

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

[hendriks_steffe]

Hi,

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

[sunny_talwar]

Isn't this what you wanted?

[hendriks_steffe]

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

[sunny_talwar · Accepted solution]

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;

[hendriks_steffe]

This works just fine. Many thanks!