Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
karunpreet
Contributor

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

Re: Invalid Expression Error

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

9 Replies
eruditio
Contributor III

Re: Invalid Expression Error

How are you using it in your data model?

karunpreet
Contributor

Re: Invalid Expression Error

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
Honored Contributor

Re: Invalid Expression Error

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

eruditio
Contributor III

Re: Invalid Expression Error

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

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

karunpreet
Contributor

Re: Invalid Expression Error

I have given the alias as 'AESTHETIC_INDICATOR'

karunpreet
Contributor

Re: Invalid Expression Error

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

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

karunpreet
Contributor

Re: Invalid Expression Error

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

Community Browser