Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]MISSING VALUES ISSUE

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]MISSING VALUES ISSUE
 Login/Join
 
Platinum Member
posted
Everyone,

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

 
Posts: 191 | Registered: September 18, 2015Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Platinum Member
posted Hide Post
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

 
Posts: 191 | Registered: September 18, 2015Report This Post
Master
posted Hide Post
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
 
Posts: 822 | Registered: April 23, 2003Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]MISSING VALUES ISSUE

Copyright © 1996-2020 Information Builders