topic Re: Invalid Expression Error in QlikView

Invalid Expression Error

Anonymous — Thu, 15 May 2014 12:27:09 GMT

Hi everyone,

I have an expression which is running perfectly in the text box but when i use the same in my data model, its throwing an error called 'Invalid Expression'. The expression is:

IF(SERVICE_INDICATOR='AESTHETIC',100*COUNT(IF(RESULT_VALUE > 15, RESULT_VALUE))/COUNT(TEST_ID))

where SERVICE_INDICATOR, RESULT_VALUE and TEST_ID are the columns of my Fact Table, and I am using the above expression in the same Fact Table.

Thanking everyone in anticipation

Re: Invalid Expression Error

Anonymous — Thu, 15 May 2014 12:29:42 GMT

How are you using it in your data model?

Re: Invalid Expression Error

Anonymous — Thu, 15 May 2014 12:41:40 GMT

NOCONCATENATE

FINAL_RESULT:

LOAD *,

IF(SERVICE_INDICATOR='AESTHETIC',(COUNT(IF(RESULT_VALUE >15, RESULT_VALUE)))/(COUNT(TEST_ID))) AS AESTHETIC_SERVICE_INDICATOR

RESIDENT RESULT;

Re: Invalid Expression Error

fkeuroglian — Thu, 15 May 2014 12:43:35 GMT

Hi, first of alla if you put in the script you have to do an AS to equal to a value

try to put this

IF(SERVICE_INDICATOR='AESTHETIC',100*COUNT(RESULT_VALUE) > 15, RESULT_VALUE)/COUNT(TEST_ID) as newfield,

good luck

Fernando

Re: Invalid Expression Error

Anonymous — Thu, 15 May 2014 12:45:16 GMT

Try including a default value?

IF(SERVICE_INDICATOR='AESTHETIC',

100*COUNT(IF(RESULT_VALUE > 15, RESULT_VALUE))/COUNT(TEST_ID), 'TEST') as AESTHETIC_SERVICE_INDICATOR

Re: Invalid Expression Error

Peter_Cammaert — Thu, 15 May 2014 12:53:15 GMT

COUNT() is an aggregation function that will work in UI expressions, but NOT in a simple LOAD without GROUP BY clause.

Re: Invalid Expression Error

Anonymous — Thu, 15 May 2014 12:58:14 GMT

I have given the alias as 'AESTHETIC_INDICATOR'

Re: Invalid Expression Error

Anonymous — Thu, 15 May 2014 13:00:26 GMT

IF I USE GROUP BY THEN I NEED TO INCLUDE ALL THE COLUMS OF THE TABLE OR THE FIELDS RELATED TO COUNT I.E SERVICE INDICATOR, RESULT VALUE AND TEST ID

Re: Invalid Expression Error

Peter_Cammaert — Thu, 15 May 2014 13:09:43 GMT

Yes, almost. It's a bit more complex.

In short: you can only include raw column names if they also appear in the GROUP BY clause. All other column names must be specified as parameters to aggregation functions like SUM(), COUNT() MAX() and the likes.

For example, this is a valid statement:

LOAD A, B, C, Max(D) AS MaximumD

RESIDENT SourceData

GROUP BY A ASC, B DESC, C;

but this is not:

LOAD A, B, C, Z, Count(E) AS NoOfOrders

RESIDENT SourceData

GROUP BY A ASC, B DESC, C;

And, if I'm not mistaken, you cannot omit columns from your LOAD list that do appear in the GROUP BY clause, like here:

LOAD C, Sum(F) AS TotalSales

RESIDENT SourceData

GROUP BY A ASC, B DESC, C;

You don't have to include all your columns if you have no need for them.

BTW See FirstSortedValue() in QlikView Help for an escape from these annoying restrictions. It's a pretty powerful function.

Best,

Peter

Re: Invalid Expression Error

Anonymous — Thu, 15 May 2014 13:29:02 GMT

Hey Peter Thanks for the explanation, I will try and let u know if it works for me or not, also I read about the First Sorted Value function, but it was not clear to me, can u explain the same with the expression that I had mentioned in my question