topic if countif expression help in QlikView

if countif expression help

l_palacios — Thu, 19 Jul 2018 17:11:30 GMT

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.

Re: if countif expression help

neelamsaroha157 — Thu, 19 Jul 2018 17:20:53 GMT

May be -

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

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

Re: if countif expression help

l_palacios — Tue, 24 Jul 2018 16:18:45 GMT

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

Re: if countif expression help

neelamsaroha157 — Tue, 24 Jul 2018 17:28:42 GMT

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.

Re: if countif expression help

l_palacios — Tue, 24 Jul 2018 18:59:53 GMT

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.

Re: if countif expression help

BalaBhaskar_Qlik — Wed, 25 Jul 2018 05:51:33 GMT

Try like this:

Table1:

Load

StopId,

noconcatenate

Table2:

Load

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

resident Table1 Group by StopId;

Drop table table1;

Re: if countif expression help

neelamsaroha157 — Wed, 25 Jul 2018 12:43:47 GMT

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.