Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
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
Highlighted
Master II
Master II

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

 

Highlighted
Contributor
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
Master II
Master II

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!

Highlighted
Contributor
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!