Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
igor_gershenson
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:

   

ItemFormula
A1/(Count of times A appears)=1/4
A1/(Count of times A appears)=1/4
A1/(Count of times A appears)=1/4
A1/(Count of times A appears)=1/4
B1/(count of times B appears)=1/2
B1/(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

Re: Formula with count function in Qlik Script

May be like this

Table:

LOAD Item,

           ....

FROM ....;

Left Join (Table)

LOAD Item,

           Count(Item) as ItemCount

Resident Table

Group By Item;

FinalTable:

LOAD *,

           1/ItemCount as NewFormula

Resident Table;

DROP Table Table;

MVP
MVP

Re: Formula with count function in Qlik Script

Table:

LOAD * inline [

Item

A

A

A

A

B

B

];

Left Join (Table)

LOAD

  Item,

    Count(Item) as ItemCount

Resident Table

Group By Item;

Left Join (Table)

LOAD

  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)

LOAD Item,

     1.0 / Count(Item) AS Formula

RESIDENT Table

GROUP BY Item;

Formula with Count function in Qlik Script thread252779.jpg

igor_gershenson
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

igor_gershenson
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.

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)

igor_gershenson
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.

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