topic Re: GROUP BY MULTIPLE in QlikView

GROUP BY MULTIPLE

Thu, 04 Jul 2013 15:25:26 GMT

Hi,

I am having an issue with my GROUP BY in my script. I am trying to GROUP BY both MARKET_SERVICE and MARKET_HOSPITAL but I keep getting the error "Invalid Expression" when I get to the Market Assessment table. I have tried the following:

- Adjusted my GROUP BY to only the one field

Can anyone tell me what I need to change in my script to get the GROUP BY to work. I have attached a copy of the script I am using to this discussion.

Thanks

Brian

Re: GROUP BY MULTIPLE

alexandros17 — Thu, 04 Jul 2013 15:49:37 GMT

when you group by all Fields different from the one used in group by must be "aggregated",

so you have to sum even

MARKET_QUANTITY

MARKET_PRICE

and aggregate with minstring function the field MARKET_SERVICE_DESC

I don't know if the result is what you expect but the syntax of group by requires aggregation function on other fields.

Hope it helps

Re: GROUP BY MULTIPLE

jagan — Thu, 04 Jul 2013 19:46:41 GMT

Hi,

If you use Group by in your statement then you have to specify all the fields used as columns in Group by otherwise you will get the error.

LOAD MARKET_SERVICE,
MARKET_HOSPITAL,
sum(MARKET_PRICE*MARKET_QUANTITY) as MARKET_REVENUE
RESIDENT Temp_Market_Assessment

GROUP BY MARKET_SERVICE,MARKET_HOSPITAL;

Service:

LOAD DISTINCT

MARKET_SERVICE,
MARKET_SERVICE_DESC

RESIDENT Temp_Market_Assessment;

DROP TABLE Temp_Market_Assessment

I think MARKET_SERVICE, MARKET_SERVICE_DESC are master data.

Hope this helps you.

Regards,

Jagan.