Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
hendriks_steffe
Contributor II
Contributor II

Count duplicates and calculate with them

Hi,

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

Item NoAttribute AAttribute BCounted duplicates
1234wa0,25
1234xb0,25
1234yc0,25
1234zd0,25
2345fe1
3456gt0,33
3456hn0,33
3456im0,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.

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

8 Replies
Anonymous
Not applicable

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
Contributor II
Contributor II
Author

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

hendriks_steffe
Contributor II
Contributor II
Author

Hi,

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

sunny_talwar

Isn't this what you wanted?

Capture.PNG

hendriks_steffe
Contributor II
Contributor II
Author

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

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
Contributor II
Contributor II
Author

This works just fine. Many thanks!