Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Lionel_Maximus
Contributor III
Contributor III

Group By Clause In Qlik

Hello,

I have an SQL Query and i am having difficulties with the Group By clause can anyone tell me how to handle it in click sense regards,

SELECT SUM (NUMBEROFCALLS) NUMBEROFCALLS,
SUM (NUMBER_OF_PRODUCTIVE_CALLS) NUMBER_OF_PRODUCTIVE_CALLS
FROM ( SELECT COUNT (PSS.STATUS_ID) NUMBEROFCALLS,
COUNT (DELIVERY.HEADER_ID) NUMBER_OF_PRODUCTIVE_CALLS,
PSS.TRN_DATE,
OUTLET_CODE
FROM PRE_SALE_STATUS PSS,
SHOP_STATUS SS,
OUTLET O,
DISTRIBUTOR D,
(SELECT S.HEADER_ID
FROM SALES S, SALES_DETAIL_VI SD
WHERE S.ID = SD.HEADER_ID AND SD.ORDER_QTY > 0
GROUP BY S.HEADER_ID) DELIVERY
WHERE PSS.STATUS_ID = SS.ID
AND PSS.OUTLET_CODE = O.CODE
AND PSS.DIST_CODE = D.CODE
AND PSS.ID = DELIVERY.HEADER_ID(+)
AND PSS.TRN_DATE BETWEEN :FDATE AND :TDATE
AND D.REGION_ID = NVL ( :P_REGION_LIST, D.REGION_ID)
--AND D.DISTRICT_ID=NVL(:P_DISTRICT_LIST,D.DISTRICT_ID)
AND D.TOWN_ID = NVL ( :P_TOWN_LIST, D.TOWN_ID)
AND D.CODE = NVL ( :P_DIST_CODE, D.CODE)
AND O.R_CODE = NVL ( :P_ROUTE_ID, O.R_CODE)
--&H_EMP
GROUP BY PSS.TRN_DATE, O.R_CODE, OUTLET_CODE)

 

 

 

6 Replies
pradosh_thakur
Master II
Master II

You cans use 

 

SQL select 

......

.......

your  queries 

.....

;

 

and your group by will be handled by SQL

 

If you want group by in Qlik you need to group it by all the available dimension else it will throw error.

 

On a lighter note It's Qlik Sense, You may want to correct it.

 

-Pradosh

Learning never stops.
Channa
Specialist III
Specialist III

remove "O.R_CODE" from group By

Channa
dwforest
Specialist II
Specialist II

This is SQL, Qlik is not the issue
You're selecting from several tables with no where or join, so likely not what you want
The syntax failure is grouping by O.R_CODE which is not in your field selection.
Running this in a SQL Editor directly would probably highlight the syntax errors better than Qlik
Lionel_Maximus
Contributor III
Contributor III
Author

Thank You For your reply......I don't want to use SQL in Qlik at all just wanna implement the logic of this query in Qlik Scripting.How to do that??

Thanks

Lionel_Maximus
Contributor III
Contributor III
Author

Hello,

Why??

Lionel_Maximus
Contributor III
Contributor III
Author

Hello,

Thank You For your reply.....I just want to implement this query logic in Qlik using Qlik Scripting....How to do that Thanks??