Formula with count function in Qlik Script

[Anonymous]

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?

[sunny_talwar]

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;

[maxgro]

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;

[Peter_Cammaert]

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;

[Anonymous]

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

[Anonymous]

How are you using the RID?

[Peter_Cammaert]

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.

[sunny_talwar]

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)

[Anonymous]

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.

[sunny_talwar]

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