I realize this question may have been asked previously, but I have not yet found the answer. I'm sure it's available on here.
I wrote this simple code off the finance table.
I have a calculation to create a percentage of change between the years. In this example, all accounts have data in both years. It's probably the same way in the master file (synonym). So, the percentage of change will be easy; however, my situation is different.
I have instances of master files (synonyms) where the synonym do not have records for particular account/year combinations. I have six years of data, and when there is data "missing" (due to not being in the master file as a record whatsoever), my percentage of change for the first year when compared to the last year will not calculate properly.
I need to create "dummy records," if you will where the account/year combination exists for all year/account combinations.
Is the easiest approach to force in nulls through the missing attribute and a define or compute in the master file? That way, I have records for all year/account combinations, or is there an another approach that someone would recommend?
TABLE FILE FINANCE
SUM
FINANCE.TOP.AMOUNT
BY FINANCE.TOP.ACCOUNT
ACROSS LOWEST FINANCE.TOP.YEAR
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,
$
ENDSTYLE
END
This message has been edited. Last edited by: JulieA,
WebFocus 8.2.04 WebFocus 8.2.04
November 06, 2020, 01:28 PM
MartinY
Look at the SQL options. You should maybe use SET ALL = PASS (or SET ALL = ON)
Another option could be to create a matrix (TABLE file) where all account will be assigned to every possible years but with 0 (zeros) as measure's value. Then you extract your real data and merge with the default file (created above). That way you will have all account/year with a valid value.
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
November 06, 2020, 02:11 PM
JulieA
Thank you for the suggestion.
In talking with my supervisor and colleague, we think we might try a similar approach but in our database first and then bring it into WebFocus as one master file. However, I love knowing it's also possible in WebFocus.
Thank you!
WebFocus 8.2.04 WebFocus 8.2.04
November 06, 2020, 04:42 PM
David Briars
quote:
..I need to create "dummy records," if you will where the account/year combination exists for all year/account combinations..
There are many ways to fill in missing data with WebFOCUS.
If your scenario is something like: A matrix report needs to show the range of Years 1981 to 1985 [George Orwell where are you?] and accounts 1000 - 2000. Data is coming from the FINANCE file and not all Year/Account combinations have data...
...then take a look at 'rows over' and 'columns and'...
SET ACROSSLINE = SKIP
SET PAGE = OFF
-RUN
TABLE FILE FINANCE
"Matrix Report"
-*
SUM AMOUNT
-*
BY ACCOUNT AS 'ACCOUNT'
ROWS
1000
OVER
1001
OVER
1002
OVER
2000
-*
ACROSS YEAR
COLUMNS 1981 AND 1982 AND 1983 AND 1984 AND 1985
-*
ON TABLE SET NODATA ':-('
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = ENIADefault, $
ENDSTYLE
END
With dialogue manager the Years and Accounts listed in the code could be dynamic, if that were a requirement.
Search for 'MacGuyver' to find an additional technique to fill in missing data/create dummy records.This message has been edited. Last edited by: David Briars,
Pilot: WebFOCUS 8.2.06 Test: WebFOCUS 8.1.05M Prod: WebFOCUS 8.1.05M Server: Windows Server 2016/Tomcat Standalone Workstation: Windows 10/IE11+Edge Database: Oracle 12c, Netezza, & MS SQL Server 2019 Output: AHTML/XLSX/HTML/PDF/JSCHART Tools: WFDS, Repository Content, BI Portal Designer & ReportCaster