topic Re: Inner Join and Group By is not working in QlikView

Inner Join and Group By is not working

Sun, 27 Jan 2013 17:57:41 GMT

Hi all,

I'm trying to fetch from SAP the capacity per day for each workcentre. Tables used are CRHD - for actual capacityID for each ObjectID (workcentre)

CRHD:
Load

   OBJID&'/'&KAPID as %CRHD_KAPA_KEY,
   OBJID as MaskinID,
   KAPID as %CapacityID_KAPID,
   WERKS as WorkCentrePlant_WERKS,
   ARBPL as Produktionsgrupp,
   STOBJ as Objectnumber_STOBJ,
   ApplyMap('CRTX',OBJID) as WorkCentreDescription,

In table KAPA I have relevant capacity for each day.

KAPA:   // Shift Parameters for Available Capacity
Inner Join
Load
   MANDT as Client_MANDT.KAPA,
   KAPID as %CapacityID_KAPID,
   VERSN as Version_VERSN.KAPA,
   if(DATUB,'9999-12-31' ) as EndDate_DATUB.KAPA,
   TAGNR as Veckodag,
SCHNR as Shiftnumber_SCHNR.KAPA,
   ANZHL as Number_ANZHL.KAPA,
   BEGZT as Start_Time_BEGZT.KAPA,
   EINZT as Operating_time_EINZT.KAPA,
   ENDZT as Finish_time_ENDZT.KAPA,
   FABTG as Workdays_FABTG.KAPA,
   KAPAZ as Capacity_KAPAZ.KAPA,
   KAPAZ/3600 as Timkapacitet,
   PAUSE as Breaktimesec._PAUSE.KAPA,
   NGRAD as Cap.util._NGRAD.KAPA,
   TPROG as Shiftdef._TPROG.KAPA,
   WOTAG as Factorycalendarday_WOTAG.KAPA,
   ANG_MIN as Min.capacity_ANG_MIN.KAPA,
   ANG_MAX as Max.capacity_ANG_MAX.KAPA;


SQL Select MANDT VERSN KAPID DATUB SCHNR BEGZT ENDZT NGRAD PAUSE ANZHL
FABTG WOTAG EINZT TAGNR KAPAZ TPROG ANG_MIN ANG_MAX from KAPA

GROUP BY CRHD~KAPID;

I want to join KAPA into CRHD and group by KAPID in CRHD but only for the same KAPID that is currently stored in CRHD.

The join works, but Group by is causing me some trouble.

Regards Magnus

Re: Inner Join and Group By is not working

Anonymous — Sun, 27 Jan 2013 18:12:05 GMT

Dear ronnerforms,

gorup by clause is not worked because aggerigation functions is required,with out aggerigation functions it will be through error.

Please use the aggerigation function and let you know

Regrads

Re: Inner Join and Group By is not working

Sun, 27 Jan 2013 20:40:03 GMT

Dear Shaik,

Forgive my small knowledge but as far as I know the Aggr function is used in expressions and dimensions when you'r building for example charts in the application ?

In my case I am building the QVD file.

Regards Magnus

Re: Inner Join and Group By is not working

Mon, 28 Jan 2013 01:18:43 GMT

Hi.

Shaik was talking about SQL.

Try this

SQL Select CRHD~KAPID, MANDT, VERSN, KAPID, DATUB, SCHNR, BEGZT, ENDZT, NGRAD, PAUSE, ANZHL,
FABTG, WOTAG, EINZT, TAGNR, KAPAZ, TPROG, sum(ANG_MIN) ANG_MIN, sum(ANG_MAX) ANG_MAX from KAPA

GROUP BY CRHD~KAPID MANDT VERSN KAPID DATUB SCHNR BEGZT ENDZT NGRAD PAUSE ANZHL

FABTG WOTAG EINZT TAGNR KAPAZ TPROG;

Alessandro Furtado

Re: Inner Join and Group By is not working

Mon, 28 Jan 2013 08:00:09 GMT

Hi, sorry. Now I see the requirement of aggr function together with Group By. Tried your suggestion but still not working.

/QTQVC/OPEN_STREAM failed after 00:00:01 Key = SQL_ERROR (ID:00 Type:E Number:001 Wrong table name or table alias name "CRHD". the field "CRHD~KAPID".)
SQL Select CRHD~KAPID, MANDT, VERSN, KAPID, DATUB, SCHNR, BEGZT, ENDZT, NGRAD, PAUSE, ANZHL,
FABTG, WOTAG, EINZT, TAGNR, KAPAZ, TPROG, sum(ANG_MIN) ANG_MIN, sum(ANG_MAX) ANG_MAX from KAPA

GROUP BY CRHD~KAPID MANDT VERSN KAPID DATUB SCHNR BEGZT ENDZT NGRAD PAUSE ANZHL
FABTG WOTAG EINZT TAGNR KAPAZ TPROG

Regards Magnus

Re: Inner Join and Group By is not working

Mon, 28 Jan 2013 10:13:50 GMT

Hi,

Group BY: is a clause used for aggregating (group) several records into one. Within one group, for a certain field, all the records must either have the same value, or the field can only be used from within an expression,

e.g. as a sum or an average.

In the SELECT statement,you will have to perform some aggregation on some measure and then

perform a group by,for all dimensions available.

Consider the below example:

Load Month, sum(Sales) as SalesPerMonth

from abc.csv group by month;

Hope this helps,

Regards,

Snehal Nabar

Re: Inner Join and Group By is not working

Mon, 28 Jan 2013 10:57:38 GMT

Hi, beginning to think Group by is the wrong approach for my goal. KAPID is the key to connect CRHD and KAPA. Solution to be is that CRHD only contains capacity times for each day linked by KAPID.