Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

romeop
New Contributor

Add field with number of record for the same key value

Hi,

i just want to add a count field with the number of records that have the same value for a key field, this is an example:

KEY   NewCountFiled

ABC   2

ABC   2

DEF   1

GHI   3

GHI   3

GHI   3

I have tried different count formulas but nome does this

Many thanks

Romeo

4 Replies
Frank_Hartmann
Honored Contributor

Re: Add field with number of record for the same key value

Maybe like this:

1:
LOAD *,RowNO() as Row  INLINE [    
    KEY,   NewCountFiled    
    ABC,   2    
    ABC,   2    
    DEF,   1    
    GHI,   3    
    GHI,   3    
    GHI,   3
];

NoConcatenate

Load *,if(KEY=Previous(KEY) and NewCountFiled=Previous(NewCountFiled),Rangesum(peek('Tmp'),1),1) as Tmp
Resident 1 ;DROP table 1;

 

Straight Table:

Dim = KEY

Expression: max(Tmp)

Unbenannt.png

 

romeop
New Contributor

Re: Add field with number of record for the same key value

Hey many thanks for the reply, but the i added the second field a the result of the count formula, it is not an input value, i just want to count the occurrences of KEY field like in Excel with a simple =COUNT.IF(A:A;rownumber) you do the trick, i didn't know you cannot do the formula just in a table but you have to write a load statement

Highlighted
Frank_Hartmann
Honored Contributor

Re: Add field with number of record for the same key value

if you do not want a script level solution then have a look at attached sample!

If this is not what you need, please show a pic of the desired output!

romeop
New Contributor

Re: Add field with number of record for the same key value

i see no sheets, only load statement inside, btw, i attach input and output image file, the new column in output should be added in a view

Many thanks!