topic Re: 'Invalid Expression Error' in QlikView

'Invalid Expression Error'

Anonymous — Sun, 18 May 2014 04:54:47 GMT

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;

Re: 'Invalid Expression Error'

SunilChauhan — Sun, 18 May 2014 05:58:23 GMT

may be you need to write

FLOOR(NUM(SAMPLE_DT)) in group by also

Re: 'Invalid Expression Error'

stevedark — Sun, 18 May 2014 10:58:20 GMT

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

Re: 'Invalid Expression Error'

hic — Sun, 18 May 2014 13:09:23 GMT

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

Re: 'Invalid Expression Error'

its_anandrjs — Sun, 18 May 2014 13:32:59 GMT

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.

Re: 'Invalid Expression Error'

Anonymous — Mon, 19 May 2014 07:20:29 GMT

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