Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
romeop
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
Frank_Hartmann
Master II
Master II

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
Contributor
Contributor
Author

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

Frank_Hartmann
Master II
Master II

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
Contributor
Contributor
Author

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!