How do I get myTOTAL to show TOTAL SUM of DEALER_COST for ALL CAR? I am not sure if I can accomplish this by DEFINE or do I need to use MATCH?
I am trying to get the following output
CAR DEALER_COST myTOTAL ALFA ROMEO 16,235 143,794 AUDI 5,063 143,794 BMW 49,500 143,794 DATSUN 2,626 143,794 JAGUAR 18,621 143,794 JENSEN 14,940 143,794 MASERATI 25,000 143,794 PEUGEOT 4,631 143,794 TOYOTA 2,886 143,794 TRIUMPH 4,292 143,794
DEFINE FILE CAR myTOTAL/D12.2= ??? ; END TABLE FILE CAR SUM DEALER_COST myTOTAL BY CAR END
WF 8.x and 7.7.x Win/UNIX/AS400, MRE/Portal/Self-Service, IIS/Tomcat, WebSphere, IWA, Realmdriver, Active Directory, Oracle, SQLServer, DB2, MySQL, JD Edwards, E-BIZ, SAP BW, R/3, ECC, ESSBASE
February 13, 2007, 01:35 PM
Francis Mariani
SET ASNAMES=ON TABLE FILE CAR SUM DEALER_COST AS 'TDEALER_COST' SUM DEALER_COST BY CAR ON TABLE HOLD AS H001 END TABLE FILE H001 PRINT DEALER_COST TDEALER_COST BY CAR END
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
February 13, 2007, 01:38 PM
Sayed
THANKS!
WF 8.x and 7.7.x Win/UNIX/AS400, MRE/Portal/Self-Service, IIS/Tomcat, WebSphere, IWA, Realmdriver, Active Directory, Oracle, SQLServer, DB2, MySQL, JD Edwards, E-BIZ, SAP BW, R/3, ECC, ESSBASE
February 13, 2007, 01:39 PM
mgrackin
Hey Francis. Look what I discovered. I had recently been playing with the WITHIN statement and thought I would try this:
TABLE FILE CAR SUM DEALER_COST DEALER_COST WITHIN TABLE BY CAR END
I love the WITHIN statement.
Thanks!
Mickey
FOCUS/WebFOCUS 1990 - 2011
February 13, 2007, 02:15 PM
Francis Mariani
TABLE FILE CAR
SUM DEALER_COST DEALER_COST WITHIN TABLE
BY CAR
END
Wow, Mickey - I did not know that!!!
quote:
You can also use WITHIN TABLE, which allows you to return the original value within a request command. The WITHIN TABLE command can also be used when an ACROSS phrase is needed without a BY phrase. Otherwise, a single WITHIN phrase requires a BY phrase.
Now that's a mouthful!
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
February 13, 2007, 02:18 PM
Sayed
What I was trying to get is the SUM TOTAL for the distinct value... Let me know what I am doing wrong
-* File sum_total2.fex SET ASNAMES=ON
TABLE FILE CAR SUM 'CNT.DST.DEALER_COST' AS 'DEALERCOST2' BY CAR ON TABLE HOLD AS H001 END
TABLE FILE H001 SUM 'CNT.DST.DEALER_COST' AS 'TDEALER_COST' SUM DEALER_COST BY CAR ON TABLE HOLD AS H002 END TABLE FILE H002 PRINT * BY CAR
END
WF 8.x and 7.7.x Win/UNIX/AS400, MRE/Portal/Self-Service, IIS/Tomcat, WebSphere, IWA, Realmdriver, Active Directory, Oracle, SQLServer, DB2, MySQL, JD Edwards, E-BIZ, SAP BW, R/3, ECC, ESSBASE
February 13, 2007, 02:21 PM
Victoria
Try this:
TABLE FILE CAR SUM DEALER_COST TOT.DEALER_COST BY CAR END
I got the exact results you put in your original post.
Victoria
February 13, 2007, 02:34 PM
Sayed
ALL 3 answers are good, but how do I incorporate that to SUM 'CNT.DST.***' on my last post?
WF 8.x and 7.7.x Win/UNIX/AS400, MRE/Portal/Self-Service, IIS/Tomcat, WebSphere, IWA, Realmdriver, Active Directory, Oracle, SQLServer, DB2, MySQL, JD Edwards, E-BIZ, SAP BW, R/3, ECC, ESSBASE
February 13, 2007, 04:18 PM
Sayed
Victoria, My expected result is
PAGE 1
CAR DEALERCOST2 TDEALER_COST ALFA ROMEO 2 17 AUDI 1 17 BMW 6 17 DATSUN 1 17 JAGUAR 2 17 JENSEN 1 17 MASERATI 1 17 PEUGEOT 1 17 TOYOTA 1 17 TRIUMPH 1 17
I am trying to SUM TOTAL on the CNT.DST.xxxx
WF 8.x and 7.7.x Win/UNIX/AS400, MRE/Portal/Self-Service, IIS/Tomcat, WebSphere, IWA, Realmdriver, Active Directory, Oracle, SQLServer, DB2, MySQL, JD Edwards, E-BIZ, SAP BW, R/3, ECC, ESSBASE
February 13, 2007, 04:19 PM
Leah
Assuming you want TOTAL COUNT OF THE CARS within the cars a count might work for you. END TABLE FILE CAR SUM CNT.DST.MODEL AS 'COUNT' DEALER_COST BY CAR ON TABLE COLUMN-TOTAL END
Leah
February 13, 2007, 04:25 PM
Sayed
Leah, I want another column where I get the TOTAL for the Distinct Counter for DEALER_COST
SET ASNAMES=ON TABLE FILE CAR SUM 'CNT.DST.DEALER_COST' AS 'DEALERCOST2' BY CAR -*ON TABLE HOLD AS H001 END
WF 8.x and 7.7.x Win/UNIX/AS400, MRE/Portal/Self-Service, IIS/Tomcat, WebSphere, IWA, Realmdriver, Active Directory, Oracle, SQLServer, DB2, MySQL, JD Edwards, E-BIZ, SAP BW, R/3, ECC, ESSBASE
February 13, 2007, 04:30 PM
Tony A
Sayed,
So you want something like this ?
DEFINE FILE CAR
DST_COUNT/I3 WITH DEALER_COST = IF DEALER_COST NE LAST DEALER_COST THEN 1 ELSE 0;
END
TABLE FILE CAR
SUM DST_COUNT AS 'Distinct,Cost,Count'
TOT.DST_COUNT WITHIN TABLE AS 'Total,Dealer,Costs'
BY CAR
ON TABLE SUMMARIZE DST_COUNT
END
-RUN
TThis message has been edited. Last edited by: Tony A,
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
February 13, 2007, 04:35 PM
Victoria
Try this:
SET ASNAMES=ON
TABLE FILE CAR SUM CNT.DST.DEALER_COST AS 'DEALERCOST2' BY CAR ON TABLE HOLD AS H001 END
MATCH FILE H001 SUM TOT.DEALERCOST2 AS 'TOTAL' BY CAR RUN
FILE H001 SUM DEALERCOST2 BY CAR AFTER MATCH HOLD AS H002 OLD-OR-NEW END TABLE FILE H002 PRINT DEALERCOST2 TOTAL BY CAR END
Victoria
February 13, 2007, 05:08 PM
Alan B
I get, with a bit of help from McGyver:
DEFINE FILE CAR BLANK/A1 WITH CAR = ' '; END TABLE FILE CAR SUM CNT.DST.DEALER_COST BY BLANK ON TABLE HOLD END JOIN BLANK WITH CAR IN CAR TO BLANK IN HOLD AS H DEFINE FILE CAR BLANK/A1 WITH CAR = ' '; END TABLE FILE CAR SUM CNT.DST.DEALER_COST H.DEALER_COST BY CAR END
Should give the results that sayed asked for at 4:18...
It is just that I am wary of using LAST with a non-key field in case there are duplicate values - been bitten by that before.
Alan. WF 7.705/8.007
February 13, 2007, 05:23 PM
Sayed
Thanks everyone! It's been great help.
WF 8.x and 7.7.x Win/UNIX/AS400, MRE/Portal/Self-Service, IIS/Tomcat, WebSphere, IWA, Realmdriver, Active Directory, Oracle, SQLServer, DB2, MySQL, JD Edwards, E-BIZ, SAP BW, R/3, ECC, ESSBASE