topic Re: condition error in QlikView

condition error

Anonymous — Thu, 05 Feb 2015 06:52:17 GMT

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,

Re: condition error

tresB — Thu, 05 Feb 2015 06:56:28 GMT

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

Re: condition error

PrashantSangle — Thu, 05 Feb 2015 07:05:11 GMT

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

Re: condition error

Anonymous — Thu, 05 Feb 2015 07:10:13 GMT

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,

Re: condition error

avinashelite — Thu, 05 Feb 2015 07:15:25 GMT

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

Re: condition error

Anonymous — Thu, 05 Feb 2015 07:17:41 GMT

Hi max ,

Thanks for your reply,

After removing -ve data its not working.

Please help me.

Thanks,

Re: condition error

tresB — Thu, 05 Feb 2015 07:22:42 GMT

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;

Re: condition error

anbu1984 — Thu, 05 Feb 2015 07:22:52 GMT

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;

Re: condition error

Anonymous — Thu, 05 Feb 2015 07:49:15 GMT

Hi anbu,

Thanks for your reply its working for me..

Thanks,