Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
How are you using it in your data model?
NOCONCATENATE
FINAL_RESULT:
LOAD *,
IF(SERVICE_INDICATOR='AESTHETIC',(COUNT(IF(RESULT_VALUE >15, RESULT_VALUE)))/(COUNT(TEST_ID))) AS AESTHETIC_SERVICE_INDICATOR
RESIDENT RESULT;
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
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
COUNT() is an aggregation function that will work in UI expressions, but NOT in a simple LOAD without GROUP BY clause.
I have given the alias as 'AESTHETIC_INDICATOR'
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
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
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