condition error

[Anonymous]

Hi,

When i using following script it gives exectution error please help me,

load 'Repeat Callers' as desc,CallDate_Date as mon, num(count(DISTINCT CLI),'#,##0.00;(#,##0.00)') as measure

resident test

where count(CLI)>1

group by CallDate_Date;

Thanks,

[tresesco]

Remove '(' from format, and try:     '#,##0.00;(#,##0.00)

[PrashantSangle]

Hi,

Apply tresseco solution, It will work for you.

But I just want to know

how can your count goes on negative?

because in this #,##0.00;(#,##0.00) format the () format execute if value goes on -ve.

so if not required remove it from your formating.

try like

num(count(DISTINCT CLI),'#,##0.00') as measure

Regards

[Anonymous]

Hi tresesco,

Thanks for your reply.After removing of '( ' also its not working.

  load 'Transfered' as desc,CallDate_Date as mon, num(count(CALLINFO_SESSIONID),'#,##0.00;(#,##0.00)') as measure

resident test

where DISPOSITION = 'XA'

group by CallDate_Date;

this script working fine

but followiing script its not working fine.I think its problem in where count(CLI)>1

load 'Repeat Callers' as desc,CallDate_Date as mon, num(count(DISTINCT CLI),'#,##0.00;(#,##0.00)') as measure

resident test

where count(CLI)>1

group by CallDate_Date;

Please help me,

Thanks,

[avinashelite]

Yes that's the issue. first get the count without where condition. Then use resident load with your condition this should work

[Anonymous]

Hi max ,

Thanks for your reply,

After removing -ve data its not working.

Please help me.

Thanks,

[tresesco]

chandini c wrote:

but followiing script its not working fine.I think its problem in where count(CLI)>1

You are right. 

Try like:

Temp:

load

      'Transfered' as desc,CallDate_Date as mon, num(count(CALLINFO_SESSIONID),'#,##0.00;(#,##0.00)') as      measure,

     Count(CLI) as Flag

resident test where DISPOSITION = 'XA' group by CallDate_Date;

Final:

Load

          measure

Resident Temp where Flag>1;

Drop table Temp;

[anbu1984 · Accepted solution]

Aggregation function cannot be used in where clause.

In Sql, Having clause can be used to give condition using aggregtion function.

In qlikview you can use preceding load as below

Load desc,mon,num(measure,'#,##0.00;(#,##0.00)') as measure where measure > 1;

load 'Repeat Callers' as desc,CallDate_Date as mon, count(DISTINCT CLI) as measure

resident test

group by CallDate_Date;

[Anonymous]

Hi anbu,

  Thanks for your reply its working for me..

Thanks,