Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
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
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.