Hi Everyone

I need an easy way to determine whether any of an undetermined amount of defined or computed fields is too small.

The reason for this is that I have found that visual discovery will crash when there is **** in the data due to a format being too small.

Now I can handle this by making all of the fields ginormous, but I do not like that idea. One because it seems like a waste of memory and two, no matter how big I make a field, it might still be too small one day. Normally I wouldn't care about this since a ***** every now and then is not the end of the world. But in the case of visual discovery, it is actually crashing the report.

So what I need is a way to know whether the format of any field has been exceeded. Has anyone ever done this? I do not want to test each value one by one with a flag along the lines of

TESTER/A1=IF SOMEFIELD GT 999999 THEN 'Y' ELSE 'N';

Ideally, I would like a way to search through the entire generated file and look for ****** all at once, from inside the fex.

I am thinking something like this... but I need it to handle a dynamic number of columns ideally.

DEFINE FILE CAR
SALES2/I6=SALES*100;
END
TABLE FILE CAR
SUM SALES SALES2 
BY COUNTRY
ON TABLE HOLD AS MYHOLD FORMAT ALPHA
END

TABLE FILE MYHOLD
PRINT E01 E02 E03
WHERE E01 EQ '******' OR E02 EQ '******' OR E03 EQ '******';
END

of course this does not work since I am looking for alpha in an integer field. I do not want to say

WHERE E01 GT 999999 OR E02 GT 999 etc etc etc;

I don't want to have to maintain the code. I simply want to know did any format turn out to be too small.

Actually, the best way I can see is to hold the data and then read the entire thing as text, looking for *****. This would work because ***** will never appear as valid data.

Once I know I have a format error I can manually fix the problem. I would simply send an email so we know about the problem before we have end users calling us complaining that the report doesn't work.

Now that I think about it, it would be nice to have an include at the end of every report that checks the data for that report for any fields that are too small. So it has to be dynamic. There could be X fields and each field's format could be anything.

Any ideas?

Thanks!

If you PRINT and HOLD just the rows and columns of data to be reported, you could compose a standard -INCLUDE fex to determine whether any of the data will overflow.

It could:
CHECK FILE holdname HOLD to gain access to the fieldnames and their Usage,
use dialog manager code to determine their positive and negative limits based on Usage,
and generate TABLE code like the following to test whether the min or max value of any column falls outside the reportable range.
skip to end of -INCUDE if ok, else take some action to signal failure.

TABLE FILE STDHOLD
WRITE
MIN.numfld1 MAX.numfld1
MIN.numfld2 MAX.numfld2
...
WHERE TOTAL
MIN.numfld1 LT &minlimit1 OR MAX.numfld1 GT &maxlimit1
OR MIN.numfld2 LT &minlimit2 OR MAX.numfld2 GT &maxlimit2
...
;
ON TABLE HOLD AS JUNK
END
-IF &LINES EQ 0 GOTO OK ;

(&LINES will be 0 if ok, 1 if there is any overflow)

Jodye,

Here is some code for your problem. Just replace CAR by the name of your file.
You will have to modify the code if you are also looking for negatives large in absolute value.

  
-* File jodye1.fex
-SET &ECHO=ON;
SET HOLDLIST = PRINTONLY
-*
-* Get the list of all fields
CHECK FILE CAR HOLD
-RUN
-*
-* Extract all numeric fields (FTYPE is 1 (I), 2 (F), 3 (D), 4 (P)
-* Find the length of the integer part: NUMLEN
-* Calculate the max value: MAXVAL
-* SAVE
TABLE FILE HOLD
PRINT FIELDNAME 
COMPUTE FT/A7=EDIT(FORMAT,'$9999999'); NOPRINT
COMPUTE INTLEN/I2=EDIT(GETTOK(FT,7,1,'.',2,'A2')); NOPRINT
COMPUTE NUMLEN/I2=IF DEC EQ 0 THEN INTLEN ELSE INTLEN - DEC - 1; NOPRINT
COMPUTE MAXVAL/D20c = 10 ** NUMLEN;
IF FTYPE LE 4
ON TABLE SAVE
END
-RUN
-*
-* Keep the number of fields found
-SET &NUMFLDS=1 * &LINES;
-SET &NUMFLDS1=&LINES -  1;
-*
-* Check for all fields if the max number is greater than what the format allows
TABLE FILE CAR
SUM
COMPUTE FLAGS0 / A&NUMFLDS = ' ';
-REPEAT #GETFLDS FOR &I FROM 1 TO &NUMFLDS;
-SET &J=&I - 1;
-READ SAVE &FNAME.A66. &MAXVAL.A20.
MAX.&FNAME 
COMPUTE FLAG&I / A1 = IF MAX.&FNAME GE &MAXVAL THEN 'Y' ELSE 'N';
COMPUTE FLAGS&I / A&NUMFLDS = SUBSTR(&NUMFLDS, FLAGS&J, 1, &NUMFLDS1, &NUMFLDS1, 'A&NUMFLDS1') || FLAG&I;  
-#GETFLDS
IF TOTAL FLAGS&NUMFLDS CONTAINS 'Y'
END
 

Good luck!

Jodye,

If you are doing a reasonableness test on your numeric fields I would encourage you to do so, but if you are just worrying about memory or storage keep in mind that both are cheap and programming time is expensive. Make your fields large enough to grow.

Hi

JG and Danny... thanks so much that is absolutely perfect. Danny your code is exactly what I need.

Pat, I know. I have a field which 99.9999% of the time contains values like "XX.X". However, every now and again the value that is calculated is some huge number. Like "XXXXXXXXXXXXX.X". This is not an error, but a valid number. I don't want to set my field lengths to accomodate such a large and bizarre number.

Anyway, once again the community pulls through. Thanks guys.

Jodye