Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

SQL group by and aggregation functions not working

Hi,

I have a SQL which I am hitting from Qlikview. The SQL is as follows:

SELECT  

  DM_STORE.LOCATION_NUMBER,

  DM_HOUR.HOUR_DESCRIPTION,

  DM_HOUR.HOUR_NUMBER,

  sum(DM_SALE_HOUR.US_AMOUNT),

  sum(DM_SALE_HOUR.UNIT_QTY)

FROM

  DM_SALE_HOUR,

  DM_HOUR,

  DM_DAY,

  DM_STORE,

  DM_PRODUCT

WHERE

  ( DM_SALE_HOUR.HOUR_ID=DM_HOUR.HOUR_ID  )

  AND  ( DM_DAY.DAY_ID=DM_SALE_HOUR.DAY_ID  )

  AND  ( DM_PRODUCT.PRODUCT_ID=DM_SALE_HOUR.PRODUCT_ID  )

  AND  ( DM_SALE_HOUR.STORE_ID=DM_STORE.STORE_ID  )

  AND  (

  (DM_PRODUCT.PSA_CATEGORY_CODE  IN  ('10.02', '10.03')

  OR   DM_PRODUCT.PSA_SUBCATEGORY_CODE  IN  ('11.01.02'))

  AND   DM_DAY.MONTH_ID  = 241

  AND  DM_HOUR.HOUR_DESCRIPTION  IN  ('2 PM to 3 PM', '3 PM to 4 PM', '4 PM to 5 PM', '5 PM to 6 PM', '6 PM to 7 PM', '7 PM to 8 PM')

  )

GROUP BY

  DM_STORE.LOCATION_NUMBER,

  DM_HOUR.HOUR_DESCRIPTION,

  DM_HOUR.HOUR_NUMBER

This doesn't work and the script stalls ( It doesn't even error out, it just stalls. The script time elapsed stalls at 1 second). But, if I remove the Group by Clause and "sum" in select statements, Qlikview pulls data. I did hit the above query in the database & it just works fine. I am not understanding what I am missing here. Please help.

Thanks,

Tutan

4 Replies
Not applicable

Re: SQL group by and aggregation functions not working

Has somebody any idea why would a aggregation and group by statement not work in this case? Can't Qlikview read a SQL in this format?

phaneendra_kunc
Not applicable

Re: SQL group by and aggregation functions not working

Have you tried running againest database directly? was it successful?

And what database?

Not applicable

Re: SQL group by and aggregation functions not working

Yes I did. Worked perfectly fine for the Oracle 9i database.

Thanks,

Tutan

phaneendra_kunc
Not applicable

Re: SQL group by and aggregation functions not working

I dont see any issue for that SQL..or that i am not aware...

May be you can do a trial and error method...

1.) Add "SQL" keyword before SELECT statement.... ana a LOAD too..

EX: Load *;

SQL Select .........;

2.) Try adding Schema Names infront of the Tables and add the Alias table names and use them..

Ex: Database01.Table_Name  x

3.) Take some of the Joins Out and see if it still throws an error....

4.)  finally Try script like this...I havent validated below once check for corrections...

Load *;

SQL SELECT

  d.LOCATION_NUMBER,

  b.HOUR_DESCRIPTION,

  b.HOUR_NUMBER,

  sum(a.US_AMOUNT),

  sum(a.NIT_QTY)

FROM

DM_SALE_HOUR a

Join DM_HOUR b

     on a.HOUR_ID=b.HOUR_ID

join DM_DAY c

      on c.DAY_ID=a.DAY_ID

      and c.DAY_ID=a.DAY_ID

Join DM_STORE d

      on a.STORE_ID=d.STORE_ID

join  DM_PRODUCT e

      on e.PRODUCT_ID=a.PRODUCT_ID

WHERE (

  (e.PSA_CATEGORY_CODE  IN  ('10.02', '10.03')

  OR   e.PSA_SUBCATEGORY_CODE  IN  ('11.01.02'))

  AND   c.MONTH_ID  = 241

  AND  b.HOUR_DESCRIPTION  IN  ('2 PM to 3 PM', '3 PM to 4 PM', '4 PM to 5 PM', '5 PM to 6 PM', '6 PM to 7 PM', '7 PM to 8 PM')

  )

GROUP BY

  d.LOCATION_NUMBER,

  b.HOUR_DESCRIPTION,

  b.HOUR_NUMBER ;