topic Re: Formula with count function in Qlik Script in QlikView

Formula with count function in Qlik Script

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Formula with count function in Qlik Script

sunny_talwar — Thu, 09 Mar 2017 17:17:20 GMT

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;

Re: Formula with count function in Qlik Script

maxgro — Thu, 09 Mar 2017 17:22:05 GMT

Table:

LOAD * inline [

Item

];

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

Peter_Cammaert — Thu, 09 Mar 2017 17:36:47 GMT

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

Table:

LOAD RowNo() AS RID, * INLINE [

Item

];

LEFT JOIN (Table)

LOAD Item,

1.0 / Count(Item) AS Formula

RESIDENT Table

GROUP BY Item;

Re: Formula with count function in Qlik Script

Anonymous — Thu, 09 Mar 2017 17:39:26 GMT

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

Re: Formula with count function in Qlik Script

Anonymous — Thu, 09 Mar 2017 17:41:02 GMT

How are you using the RID?

Re: Formula with count function in Qlik Script

Peter_Cammaert — Thu, 09 Mar 2017 17:41:48 GMT

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

sunny_talwar — Thu, 09 Mar 2017 17:42:33 GMT

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)

Re: Formula with count function in Qlik Script

Anonymous — Thu, 09 Mar 2017 18:36:40 GMT

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

sunny_talwar — Thu, 09 Mar 2017 18:47:18 GMT

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

Re: Formula with count function in Qlik Script

Anonymous — Thu, 09 Mar 2017 19:09:32 GMT

I pulled out every single field from my database to get all the rows with no filters and I still saw only 11 rows for that item, so where would 51 come from?

Re: Formula with count function in Qlik Script

sunny_talwar — Thu, 09 Mar 2017 19:13:24 GMT

I won't be able to tell you much because I don't have your app to look at. May be you can share a sample or share some images.

Re: Formula with count function in Qlik Script

MarcoWedel — Fri, 10 Mar 2017 00:52:59 GMT

which object did you use to display those 11 rows?

Re: Formula with count function in Qlik Script

muthukumar77 — Fri, 10 Mar 2017 11:34:50 GMT

Table:

LOAD RowNo() AS ID, * INLINE [

Item

];

Try like this,

Dimension: ID, Item

Expression: 1/Count(Total <Item> Item)