Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Go
New
Search
Notify
Tools
Reply
  
Across
 Login/Join
 
Silver Member
posted
Is there a way to sort an across by sum value type ? Like a pivot table ?

SUM
USD
LCL
BY BUSINESS_NAME
ACROSS CALENDAR_YYYY


for example...

currently the data shows..
2007 2008
Company A USD LCL USD LCL
Company B USD LCL USD LCL

but i would like...
2007 2008
Company A USD USD LCL LCL
Company B USD USD LCL LCL


WebFocus 7.11/7.6
 
Posts: 38 | Registered: May 20, 2004Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Stephen,
Here is a possibility (using the CAR file):
  
-* File Stephen.fex
TABLE FILE CAR
SUM RCOST DCOST
BY COUNTRY
BY SEATS
ON TABLE SAVE AS STEPHEN
END
DEFINE FILE STEPHEN
TYP/A5=IF NUM EQ 1 THEN 'RCOST' ELSE 'DCOST';
END
TABLE FILE STEPHEN
SUM COST
BY COUNTRY
ACROSS NUM NOPRINT
ACROSS SEATS
ACROSS TYP AS ''
END

And here is the stephen.mas master file:
  
 FILENAME=STEPHEN    , SUFFIX=FIX     , $
 SEGMENT=STEPHEN
 FIELDNAME=COUNTRY, ALIAS=E01, USAGE=A10, ACTUAL=A10, $
 FIELDNAME=SEATS, ALIAS=E02, USAGE=I3, ACTUAL=A03, $
 SEGMENT=COST, PARENT=STEPHEN, OCCURS=VARIABLE
 FIELDNAME=COST, ALIAS=E03, USAGE=D7, ACTUAL=A07, $
 FIELDNAME=NUM, ALIAS=ORDER, USAGE=I2, ACTUAL=I4, $


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1954 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
There are several ways to do this.

SET ASNAMES=ON
DEFINE FILE XXX
AMCODE/A3='USD';
END
TABLE FILE XXX
SUM USD AS 'AMOUNTX'
BY CALENDAR_YYYY
BY BUSINESS_NAME
BY AMCODE
ON TABLE HOLD AS HOLDUSD
END
DEFINE FILE XXX
AMCODE/A3='LCL';
END
TABLE FILE XXX
SUM LCL AS 'AMOUNTX'
BY CALENDAR_YYYY
BY BUSINESS_NAME
BY AMCODE
ON TABLE HOLD AS HOLDLCL
END
-RUN
TABLE FILE HOLDUSD
PRINT *
ON TABLE HOLD AS HOLDTOTAL
MORE
FILE HOLDLCL
END
-RUN
TABLE FILE HOLDTOTAL
SUM AMOUNTX AS ''
BY BUSINESS_NAME
ACROSS CALENDAR_YYYY
ACROSS HIGHEST AMCODE NOPRINT
END


I supposed the USD and LCL have the same size.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Reply With QuoteReport This Post
Silver Member
posted Hide Post
Thanks fellas -

Daniel, ill give it a try.

Frank - the one drawback to this solution that i couldnt get around is that the user wants the values formatted differently between the two columns (ie LCL with 2 decimals, USD with 0). When i put them both into the same field, i couldnt split the formatting.


WebFocus 7.11/7.6
 
Posts: 38 | Registered: May 20, 2004Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Well Stephen, that different format is also in Daniel's solution a problem.

But you can solve that too.

In your define you create a field like

FMAT/A8=IF AMCODE EQ 'USD' THEN 'D12.2S' ELSE 'D7.1%';

now change the the line
"SUM AMOUNTX AS ''" to
SUM AMOUNTX/FMAT AS ''




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 


Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.