Hi

I have been challenged with creating a dynamic white list based on the fields and values within the master files.

So far I have the code to generate the master file list and then use this to create a list of all fields within each master file.

Where I am then having difficulty is then returning the column name and values. The output I would like is :
COL1 COL2
Attribute AllowedValue
MODEL 100 LS 2 DOOR AUTO
MODEL 2000 4 DOOR BERLINA
MODEL 2000 GT VELOCE

I have attached below me code to get me this far, any advice would be greatfully received.

Thanks

Mark

TABLE FILE SYSTABLE
PRINT NAME
WHERE REMARKS NE ' '
ON TABLE HOLD AS Masters FORMAT ALPHA
END

-SET &RECCOUNT = &LINES;
-*
-RUN
-SET &I=0;
-START
-SET &I=&I+1;
-READ Masters &MasterFile.A50.

-SET &MasterFileUC=UPCASE(50,&MasterFile,'A50');

-TYPE &MasterFileUC

CHECK FILE &MasterFile HOLD
TABLE FILE HOLD
PRINT FIELDNAME/A100 AND FILENAME/A100
BY FIELDNAME
BY FILENAME
WHERE FILENAME = &MasterFileUC.QUOTEDSTRING;
ON TABLE HOLD AS MasterFields FORMAT ALPHA
END

TABLE FILE SYSCOLUM
PRINT NAME/A100 AND PROPERTY AND FILENAME/A100
BY NAME
BY FILENAME
WHERE PROPERTY NE 'MEASURE'
ON TABLE HOLD AS NonMeasureFields FORMAT ALPHA
END
-*
-*
JOIN CLEAR *
JOIN INNER FIELDNAME AND FILENAME IN MasterFields TO NAME AND FILENAME IN NonMeasureFields
END
-*
TABLE FILE MasterFields
PRINT NAME AND FILENAME
WHERE NAME NE ' '
ON TABLE HOLD AS WhiteListAttibutes FORMAT ALPHA
END
-*
-SET &WHTLSTCOUNT = &LINES;
-*
-RUN
-SET &II=0;
-INNERSTART
-SET &II=&II+1;
-READ WhiteListAttibutes &WhiteListAttibute.A100.
-*PROBLEM BEGINS HERE ---->
DEFINE FILE &MasterFile
Attribute/A20=&WhiteListAttribute;
END

TABLE FILE &MasterFile
PRINT Attribute AND &WhiteListAttribute;
END

-IF &II LT &WHTLSTCOUNT THEN GOTO INNERSTART;
-INNERENDLOOP

-IF &I LT &RECCOUNT THEN GOTO START;
-ENDLOOP

This message has been edited. Last edited by: faddybloom,

quote:

-READ WhiteListAttibutes &WhiteListAttibute.A100.
-*PROBLEM BEGINS HERE ---->
DEFINE FILE &MasterFile
Attribute/A20=&WhiteListAttribute;
END

TABLE FILE &MasterFile
PRINT Attribute AND &WhiteListAttribute;
END

Nearly there!

Firstly, in the file WhiteListAttributes the NAME is 66 characters and the FILENAME is 64 characters.
Second, the Attribute DEFINEd field should match the format of &WhiteListAttribute.
Third, the &WhiteListAttribute in the DEFINE should be in quotes
Fourth, the PRINT should not end with a ;
Lastly, &WhiteListAttibute.A100. is spelt differently on -READ and its use.

I would use the NOCLOSE option on the -READ, followed by the CLOSE. This allows you to -RUN each TABLE request without losing place in the file. Also I would place a TRUNCATE in to keep things neater and use SAVE rather than HOLD.

TABLE FILE MasterFields
PRINT NAME AND FILENAME
WHERE NAME NE ' '
ON TABLE SAVE AS WhiteListAttributes FORMAT ALPHA
END
-*
-SET &WHTLSTCOUNT = &LINES;
-*
-RUN
-SET &II=0;
-INNERSTART
-SET &II=&II+1;
-READ WhiteListAttributes NOCLOSE &WhiteListAttribute.A64.
-SET &WhiteListAttribute = TRUNCATE(&WhiteListAttribute);
-*PROBLEM NO LONGER BEGINS HERE ---->
DEFINE FILE &MasterFile
Attribute/A64='&WhiteListAttribute';
END

TABLE FILE &MasterFile
PRINT Attribute AND &WhiteListAttribute
END
-RUN

Which should give you a starting point to continue.

Thanks Alan!!

Points taken onboard and implemented...

I now have a working dynamic white list .

Code below:

-* ---------------------------------------------------------------------------------------------------
-* EXRACT COMPLETE LIST OF MASTER FILES INTO A HOLD FILE
-* ---------------------------------------------------------------------------------------------------
TABLE FILE SYSTABLE
PRINT NAME
-*WHERE REMARKS NE ' '
WHERE NAME EQ 'disc_filesize_summary' -*OR
-*'volumes_cube'
ON TABLE HOLD AS Masters FORMAT ALPHA
END

-* ---------------------------------------------------------------------------------------------------
-* SET VARIABLES REQUIRED FOR LOOP THROUGH OF MASTER FILES, AND INITIATE LOOP
-* ---------------------------------------------------------------------------------------------------
-SET &RECCOUNT = &LINES;
-*
-RUN
-SET &I=0;
-START
-SET &I=&I+1;
-* ---------------------------------------------------------------------------------------------------
-* READ THE Masters HOLD FILE LINE BY LINE
-* ---------------------------------------------------------------------------------------------------
-READ Masters &MasterFile.A50.
-* ---------------------------------------------------------------------------------------------------
-* CONVERT FIELD TO UPPERCASE
-* ---------------------------------------------------------------------------------------------------
-SET &MasterFileUC=UPCASE(50,&MasterFile,'A50');

-* ---------------------------------------------------------------------------------------------------
-* EXRACT COMPLETE LIST OF FIELDNAMES FROM EACH MASTER FILE
-* ---------------------------------------------------------------------------------------------------
CHECK FILE &MasterFile HOLD
TABLE FILE HOLD
PRINT FIELDNAME/A100 AND FILENAME/A100
BY FIELDNAME NOPRINT
BY FILENAME NOPRINT
WHERE FILENAME = &MasterFileUC.QUOTEDSTRING;
ON TABLE HOLD AS MasterFields FORMAT ALPHA
END

-* ---------------------------------------------------------------------------------------------------
-* EXRACT COMPLETE LIST OF FIELDNAMES WHERE NOT A MEASURE FIELD
-* ---------------------------------------------------------------------------------------------------
TABLE FILE SYSCOLUM
PRINT NAME/A100 AND PROPERTY AND FILENAME/A100
BY NAME NOPRINT
BY FILENAME NOPRINT
WHERE PROPERTY NE 'MEASURE'
ON TABLE HOLD AS NonMeasureFields FORMAT ALPHA
END
-*-*
-*-*
-* ---------------------------------------------------------------------------------------------------
-* JOIN THE TWO DATA SETS
-* ---------------------------------------------------------------------------------------------------
JOIN CLEAR *
JOIN INNER FIELDNAME AND FILENAME IN MasterFields TO NAME AND FILENAME IN NonMeasureFields
END
-*-*
-* ---------------------------------------------------------------------------------------------------
-* STORE THE RESULT FROM THE JOIN IN A HOLD FILE
-* ---------------------------------------------------------------------------------------------------
TABLE FILE MasterFields
PRINT NAME
WHERE NAME NE ' '
ON TABLE SAVE AS WhiteListAttributes FORMAT ALPHA
END
-*
-* ---------------------------------------------------------------------------------------------------
-* SET VARIABLES REQUIRED FOR LOOP THROUGH OF FIELDS, AND INITIATE LOOP
-* ---------------------------------------------------------------------------------------------------
-SET &WHTLSTCOUNT = &LINES;
-*
-SET &II=0;
-INNERSTART
-SET &II=&II+1;
-* ---------------------------------------------------------------------------------------------------
-* READ EACH FIELD
-* ---------------------------------------------------------------------------------------------------
-READ WhiteListAttributes NOCLOSE &AllowedValue.A100.
-SET &AllowedValue = TRUNCATE(&AllowedValue);
-*
DEFINE FILE &MasterFile
Attribute/A100='&AllowedValue';
AllowedValue/A100=&AllowedValue;
END
-* ---------------------------------------------------------------------------------------------------
-* INTERROGATE DATA SOURCES AND RETURN VALUES
-* ---------------------------------------------------------------------------------------------------
TABLE FILE &MasterFile
BY Attribute
BY AllowedValue
ON TABLE HOLD AS Src
END
-* ---------------------------------------------------------------------------------------------------
-* MERGE DATA SETS INTO ONE FINAL WHITELIST
-* ---------------------------------------------------------------------------------------------------
-IF &II EQ 1 THEN CONTINUE ELSE GOTO MERGE;

TABLE FILE Src
PRINT Attribute AND AllowedValue
ON TABLE HOLD AS Whitelist2
END

-MERGE
TABLE FILE Src
PRINT Attribute AND AllowedValue
ON TABLE HOLD AS Whitelist3
MORE
FILE Whitelist2
END

TABLE FILE Whitelist3
PRINT Attribute AND AllowedValue
ON TABLE HOLD AS Whitelist2
END
-*
-IF &II LT &WHTLSTCOUNT THEN GOTO INNERSTART;
-INNERENDLOOP

-IF &I LT &RECCOUNT THEN GOTO START;
-ENDLOOP