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,

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.

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!

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,