topic Re: Average in Load Resident Table not working getting invalid expression error ? in QlikView

Average in Load Resident Table not working getting invalid expression error ?

Tue, 03 Jun 2014 18:59:18 GMT

Hi,

I am new to qlikview.

any help would b appreciated.

I am trying to do calculate Average with group by in Load Resident statement but it's not working.

I am getting invalid expression error.

Example :

TEST:

Load
OrderNumber,

AVG(Items)

Resident Test1
GROUP BY
OrderNumber;

can someone help me ?

Re: Average in Load Resident Table not working getting invalid expression error ?

jpapador — Tue, 03 Jun 2014 19:01:45 GMT

Have you tried aliasing the field so:

Avg(Items) as AvgItems

Re: Average in Load Resident Table not working getting invalid expression error ?

its_anandrjs — Tue, 03 Jun 2014 19:02:31 GMT

Try different way see the load script

Eg:-

Test1:

Load * from Location;

Noconcatenate

TEST:

Load
OrderNumber,

AVG(Items) as Items //Give the name to this file is important

Resident Test1
GROUP BY
OrderNumber;

Drop Table Test1;

Re: Average in Load Resident Table not working getting invalid expression error ?

Tue, 03 Jun 2014 19:08:16 GMT

thanks for your reply.

I had tried and gave alias,

but, still, it's not working

do you have any other suggestions ?

Re: Average in Load Resident Table not working getting invalid expression error ?

Tue, 03 Jun 2014 19:10:08 GMT

TEST:

Load
OrderNumber,

AVG(Items) as [Item Avg]

Resident Test1
GROUP BY
OrderNumber;

Re: Average in Load Resident Table not working getting invalid expression error ?

Tue, 03 Jun 2014 19:13:40 GMT

Thanks for your reply.

still, it's not working.

do you have any other alaternatives ?

Re: Average in Load Resident Table not working getting invalid expression error ?

Tue, 03 Jun 2014 19:15:10 GMT

Do you have your qvw we can look at?

Re: Average in Load Resident Table not working getting invalid expression error ?

jpapador — Tue, 03 Jun 2014 19:19:27 GMT

I think anand may be onto something, try putting the no concatenate before the load, so like:

TEST:

Noconcatenate Load
OrderNumber,

AVG(Items) as Items

Resident Test1
GROUP BY
OrderNumber;

Drop Table Test1;

Re: Average in Load Resident Table not working getting invalid expression error ?

eduardo_sommer — Tue, 03 Jun 2014 19:40:55 GMT

Hi, Can you please share a sample of the Test1 Table with us? I would like to understand the meaning of the field items. Is it the number of items of an entire Order?

Eduardo

Re: Average in Load Resident Table not working getting invalid expression error ?

maxgro — Tue, 03 Jun 2014 20:35:00 GMT

post the logfile of your reload

same name as your qlik doc, same folder,

enable it in menu --> settimgs --> document --> general --> generate logfile

Re: Average in Load Resident Table not working getting invalid expression error ?

Tue, 03 Jun 2014 20:39:21 GMT

Can u post some sample data? What you have for items and Orderno?

Re: Average in Load Resident Table not working getting invalid expression error ?

its_anandrjs — Tue, 03 Jun 2014 20:42:15 GMT

It seems some problem in the script but it looks ok and it works perfect as you written like without aliasing it will works but check the sample file which is example same script like with your load script.

Test1:

LOAD * Inline

[

OrderNumber,Items

1,1200

2,1235

1,1235

3,1500

2,1545

3,2323

1,2456

3,4811

];

TEST:

Load

OrderNumber,

AVG(Items) as [Items Avg]

Resident Test1

GROUP BY

OrderNumber;

DROP Table Test1;

Re: Average in Load Resident Table not working getting invalid expression error ?

Wed, 04 Jun 2014 13:40:28 GMT

thanks it works now.