Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Invalid Expression Error

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

1 Solution

Accepted Solutions
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

View solution in original post

9 Replies
Anonymous
Not applicable
Author

How are you using it in your data model?

Anonymous
Not applicable
Author

NOCONCATENATE

FINAL_RESULT:

LOAD *,

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

RESIDENT RESULT;

fkeuroglian
Partner - Master
Partner - Master

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

Anonymous
Not applicable
Author

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

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

Anonymous
Not applicable
Author

I have given the alias as 'AESTHETIC_INDICATOR'

Anonymous
Not applicable
Author

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

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

Anonymous
Not applicable
Author

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