if countif expression help

[l_palacios]

Can someone help? Trying to create an expression in QlikView that works like the following excel formula:

=IF(COUNTIF($AE$2:AE2,AE2)>1,"Add On","Stop")

Thanks in advance.

[neelamsaroha157]

May be -

If(Count(Field)>1, 'Add On', 'Stop')

Are you trying to do it in script or front end?

[l_palacios]

Hi yes, originally, i tried using the following in my script:

If(Count([StopID])>1,'Add On','Stop')    as Stops,

but i keep getting "Invalid Expression" error

[neelamsaroha157]

If you are using Count or any other aggregation function in your load statement then you need to use 'group by' clause. May be that's the reason for your invalid expression error.

[l_palacios]

i will try that...otherwise, i may just convert the output to a chart table and just use a set analysis count function. Appreciate the help.

[balabhaskarqlik]

Try like this:

Table1:

Load

    StopId,

B,

C;

noconcatenate

Table2:

Load

If(Count(StopID)>1,'Add on','Stop') as Stops,

B,

C

resident Table1 Group by StopId;

Drop table table1;

[neelamsaroha157]

Hi Bala, I think the 'group by' clause need to include all the fields in the Load statement. I would suggest to load only StopId and the if statement in the table2 and skip dropping the table1.