Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Everyone,
I am using Count function in my script also I am using Group By with it, but I am always getting the same invalid expression error. Please help me with it, as its urgent
My script goes like this
NOCONCATENATE
FINAL_RESULT:
LOAD TEST_ID,
RESULT_ID,
RESULT_AUTH_DT,
RESULT_ENTRY_DT,
COMPONENT_NAME,
RESULT_TYPE,
RESULT_STAT,
RESULT_TEXT,
RESULT_UNIT,
RESULT_VALUE,
SERVICE_INDICATOR,
SAMPLE_ID,
TEST_STAT,
TEST_STARTED_DT,
TEST_COMPLETED_DT,
TEST_AUTH_DT,
SAMPLE_POINT_ID,
SAMPLE_ID_TEXT,
SAMPLE_STAT,
SAMPLE_DT,
SAMPLE_RECEIVE_DT,
COMPLETED_DT,
SAMPLE_AUTH_DT,
SAMPLE_COLLECTED_ADDR,
DESC,
PRODUCT_ID,
PRODUCT_VERSION,
SAMPLE_POINT_DESC,
FLOOR(NUM(SAMPLE_DT)) AS [%Link_DATE_Num],
IF(SERVICE_INDICATOR='AESTHETIC',(COUNT(IF(RESULT_VALUE >15, RESULT_VALUE))/(COUNT(TEST_ID)))) AS AESTHETIC_SERVICE_INDICATOR
FROM
[...\RESULT.qvd]
(qvd)
group by TEST_ID,
RESULT_ID,
RESULT_AUTH_DT,
RESULT_ENTRY_DT,
COMPONENT_NAME,
RESULT_TYPE,
RESULT_STAT,
RESULT_TEXT,
RESULT_UNIT,
SERVICE_INDICATOR,
SAMPLE_ID,
TEST_STAT,
TEST_STARTED_DT,
TEST_COMPLETED_DT,
TEST_AUTH_DT,
SAMPLE_POINT_ID,
SAMPLE_ID_TEXT,
SAMPLE_STAT,
SAMPLE_DT,
SAMPLE_RECEIVE_DT,
COMPLETED_DT,
SAMPLE_AUTH_DT,
SAMPLE_COLLECTED_ADDR,
DESC,
PRODUCT_ID,
PRODUCT_VERSION,
SAMPLE_POINT_DESC;
may be you need to write
FLOOR(NUM(SAMPLE_DT)) in group by also
You need to ensure that every field that is not listed in the GROUP BY list appears in an aggregation function.
So you could either change your date function to:
min(FLOOR(NUM(SAMPLE_DT))) AS [%Link_DATE_Num],
Or add SAMPLE_DT to the GROUP BY list, you would not need to add the entire function to the list.
-Steve
It seems to me as if this is the wrong place to put your Count() function. Why don't you load your QVD as it is, and then create a chart with your expression?
HIC
Add SAMPLE_DT in the group bu clause or why you transform the SAMPLE_DT field in this load if you have on more stage above this load you can do this there also. And i believe you can use count expression in your front end also there is no need for this count here. But if you want to do this in load script then you can load another resident load table for this.
Thnaks to all for replying, I will try the solutions given by you all and its mandatory for me to use this count in the back end, otherwise I would have also used this is in the front end