I'm having some difficulty with some code and need some assistance.
I need to create a counter/grouping in a DEFINE based on a Start Date and End Date to place into an ACROSS.
Start Date: 2015/10/15
End Date: 2016/02/15
2015/10 = 1
2015/11 = 2
2015/12 = 3
2016/01 = 4
2016/02 = 5
Any assistance will be greatly appreciated.
TIAThis message has been edited. Last edited by: <Emily McAllister>,
Could be one way to do it
DEFINE FILE GGSALES DTEYM /YYM = DATE; END TABLE FILE GGSALES SUM UNITS BY DTEYM BY TOTAL COMPUTE ID /P3 = IF DTEYM EQ LAST DTEYM THEN ID ELSE ID + 1; BY STCD ON TABLE HOLD AS TMP FORMAT FOCUS END -RUN TABLE FILE TMP SUM UNITS BY STCD ACROSS ID ACROSS DTEYM AS '' END -RUN
WF versions : Prod 18.104.22.168M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
Sounds like a candidate for the McGyver technique.
Or, if you're reporting on an RDBMS that supports recursive CTE's or that has generator functions (or both), you could do it in SQL (using SQL passthru).
Especially the CTE's are fun queries to write, but you need to get your head in the right mode to understand how that recursion works.
WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
How about using RANKED BY Start Date?
Run the request and hold the result and you will have a new field called RANK that you can subsequently use as you please.
WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
TABLE FILE SWI
WHERE TIME_TIME_HIERARCHY_WEEK GE '&START_DATE';
WHERE TIME_TIME_HIERARCHY_WEEK LE '&END_DATE';
ON TABLE HOLD
DEFINE FILE HOLD
NEWSORT/I1=IF TIME_TIME_HIERARCHY_WEEK NE LAST TIME_TIME_HIERARCHY_WEEK THEN LAST NEWSORT + 1 ELSE 1;
TABLE FILE HOLD
Windows, All Outputs
Thank You for everyone's reply...the stakeholder dropped the requirement I needed the counter for from the report.
Thanks for all the great suggestions.
|Powered by Social Strata|