# New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
New Contributor III

## Formula with count function in Qlik Script

Hi,

Need urgent help here.

I need to create a formula for each item that equals 1/Number of times that item appears in table. Example:

 Item Formula A 1/(Count of times A appears)=1/4 A 1/(Count of times A appears)=1/4 A 1/(Count of times A appears)=1/4 A 1/(Count of times A appears)=1/4 B 1/(count of times B appears)=1/2 B 1/(count of times B appears)=1/2

What is the easiest way to do this?

I tried creating a mapping table that counts Items and mapping it back to the original table but it is not working (sometimes the denominators are way too big). Any other way quick way to do this?

Tags (1)
13 Replies
MVP

## Re: Formula with count function in Qlik Script

May be like this

Table:

....

FROM ....;

Left Join (Table)

Count(Item) as ItemCount

Resident Table

Group By Item;

FinalTable:

1/ItemCount as NewFormula

Resident Table;

DROP Table Table;

MVP

## Re: Formula with count function in Qlik Script

Table:

Item

A

A

A

A

B

B

];

Left Join (Table)

Item,

Count(Item) as ItemCount

Resident Table

Group By Item;

Left Join (Table)

Item,

only(1/ItemCount) as Formula

Resident Table

Group By Item;

## Re: Formula with count function in Qlik Script

Thanks for the preparatory work, guys. I think you can do it even shorter:

Table:

LOAD RowNo() AS RID, * INLINE [

Item

A

A

A

A

B

B

];

LEFT JOIN (Table)

1.0 / Count(Item) AS Formula

RESIDENT Table

GROUP BY Item;

New Contributor III

## Re: Formula with count function in Qlik Script

Sorry but could you explain this? How are you using the row function? Thanks

New Contributor III

## Re: Formula with count function in Qlik Script

How are you using the RID?

## Re: Formula with count function in Qlik Script

Sorry about the delay. You can drop the RowNo() call. I just use it to be able to show the result in a Table Box (identical rows will only show up once). Image added.

Good luck.

MVP

## Re: Formula with count function in Qlik Script

RID is not used for anything in what you need. It is just to display the result in different lines. Without RID, you would have just seen two rows of data (in this example)

New Contributor III

## Re: Formula with count function in Qlik Script

Thank you! The script appears to work but I am having issues with the count portion of this still. In other words, if an item appears, say, 11 times, the count returns 51, which makes no sense. Any idea why this may be happening? I have never seen this before.

MVP

## Re: Formula with count function in Qlik Script

It may appear as 11 rows, but in fact there are more rows for the item in the database or in your application. You can check this using a front end object where add Item as a dimension and Count(Item) as expression. This will tell you how many times a particular item repeats in your application