1 / 45

FILTERS and FUNCTIONS: Hidden Gems

FILTERS and FUNCTIONS: Hidden Gems. Walter F. Blood Technical Director, FOCUS Division June, 2008. FILTERS and FUNCTIONS FILTERs. TWO Types of Filters Visible Stored in Master File Activated by reference in a request Invisible Independent command Executable in PROFILEs, or FOCEXECs

niabi
Télécharger la présentation

FILTERS and FUNCTIONS: Hidden Gems

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. FILTERS and FUNCTIONS: Hidden Gems Walter F. Blood Technical Director, FOCUS Division June, 2008

  2. FILTERS and FUNCTIONS FILTERs TWO Types of Filters • Visible • Stored in Master File • Activated by reference in a request • Invisible • Independent command • Executable in PROFILEs, or FOCEXECs • Active for the entire session • Added to every request using the Master

  3. FILTERS and FUNCTIONSVisible FILTERs What is a visible FILTER? • Virtual Field stored in Master File. • Must evaluate to True or False – 1 or 0. • Format is always I1. • Only evaluated when referenced in your request. • Functional with • CHECK FILE • TABLE/TABLEF FILE • MATCH FILE • MORE • SQL

  4. FILTERS and FUNCTIONSVisible FILTER Visible FILTER syntax FILTER <filtername> = expression;$ • Positioned anywhere in Master. • Use any expression legal for WHERE • Usable with Business View or Join. • Shortest unique truncation will access it. • FILTER TOPSELLERS = SALES GT 20000;$

  5. FILTERS and FUNCTIONSVisible FILTER Expressions WHERE (expression) (operator) (expression) AND/OR … (field/literal/function) (operator) (field/literal/function) • Each expression has one or more operators • Can use real and virtual fields in the Master file. • Cannot use virtual fields created in the request or fields described in other files. • Selections based on fields from multiple files must be AND/OR in the WHERE phrase – not in the FILTER. • IF..THEN..ELSE and DECODE to Alpha are not supported in FILTERs.

  6. FILTERS and FUNCTIONSVisible FILTER Referencing Visible FILTERs WHERE <filtername> • No limit to the number of Filters. • FILTER from Master File is automatically included on reference in request. • Beware collisions– multiple WHEREs are ANDed together. From Master and Request! • Be precise not creative with the Filter Names – indicate what it does.

  7. FILTERS and FUNCTIONSVisible FILTER Filter Naming • Think of your endusers and how they see the data! • Combine complex selection criteria into one filter! • Give the filter a meaningful name! • FILTER1 – probably useless • FROMLASTWEEK - better

  8. FILTERS and FUNCTIONSVisible FILTER Examples • GETWKLYBONUS=((WKLYSALES/DOLQUOTA) GT 1.200 AND • (WKLYUNITS/UNTQUOTA) GT 1.100) • OR ((TOTSALES/TOTDOLQUOTA) GT 1.500) • OR ((TOTUNITS/TOTUNTQUOTA) GT 2.000);$ REVIEW4RAISE = HIRE_DATE LT 810101 AND CURR_SAL LT 12500 AND CURR_JOBCODE CONTAINS ‘A’;$

  9. FILTERS and FUNCTIONSInvisible FILTERs What is an invisible FILTER? And how is it different from a visible FILTER? • Activated anywhere in session. • Not referenced in request. • Controlled with commands • Declared with FILTER FILE command. • Activated/Deactivated with SET FILTER command. • Requires SET KEEPFILTERS to cross JOINs • Status of FILTER can be queried -? FILTER. • Supports Defines available only to FILTERs. • Allows both WHERE and IF syntax.

  10. FILTERS and FUNCTIONSInvisible FILTERs FILTER FILE command • FILTER FILE <filename> [ CLEAR | ADD ] • [filter-defines;] • NAME=filtername1 [ ,DESC=text] • where-if phrases • . • . • NAME=filternamen [ ,DESC=text] • where-if phrases • END

  11. FILTERS and FUNCTIONSInvisible FILTERs FILTER FILE command • FILTER FILE <filename> [ CLEAR | ADD ] • [filter-defines;] • NAME=filtername1 [ ,DESC=text] • where-if phrases • . • . • NAME=filternamen [ ,DESC=text] • where-if phrases • END Describes the filter and can be issued in profile, focexec, or command line Adds new filter phrases to existing FILTER phrases and defines for this file. Deletes existing FILTER phrases and defines for this file.

  12. FILTERS and FUNCTIONSInvisible FILTERs FILTER FILE command • FILTER FILE <filename> [ CLEAR | ADD ] • [filter-defines;] • NAME=filtername1 [ ,DESC=text] • where-if phrases • . • . • NAME=filternamen [ ,DESC=text] • where-if phrases • END Only referenceable in Filters! Virtual fields used in Filters. More on this later!

  13. FILTERS and FUNCTIONSInvisible FILTERs FILTER FILE command Filter Name must be 8 characters or less and unique for the master file. Used by SET FILTER= • FILTER FILE <filename> [ CLEAR | ADD ] • [filter-defines;] • NAME=filtername1 [ ,DESC=text] • where-if phrases • . • . • NAME=filternamen [ ,DESC=text] • where-if phrases • END Only one line of text allowed Any valid WHERE or IF screening conditions. Must reference filter defines, or real or virtual fields in Master only.

  14. FILTERS and FUNCTIONSInvisible FILTER Filter Define Limitations • Local to specific filter declaration • Cannot be used in DEFINE or TABLE • Supports standard DEFINE functionality • Unlike DEFINE, do not count toward display field limit • Must all be declared before first named filter • Cannot reuse another virtual field from the same file.

  15. FILTERS and FUNCTIONSInvisible FILTER Activating and Deactivating FILTERs SET FILTER = * IN <filename> ON/OFF ALL declared filters. Be careful using this setting. SET FILTER = name1 name2 …namen IN <filename ON/OFF Only named declared filters will be affected.

  16. FILTERS and FUNCTIONSInvisible FILTER Filter Declaration Example FILTER FILE CAR ADD PROFIT/D7=(RCOST-DCOST); MARGINPCT/D7.2=(PROFIT/DCOST)*100 ; NAME=HIMARGIN WHERE MARGINPCT GT 20.0 NAME=MDMARGIN WHERE MARGINPCT FROM 10.0 to 20.0 NAME=LOMARGIN WHERE MARGINPCT LT 10.0 END

  17. FILTERS and FUNCTIONSInvisible FILTER > > set filter = himargin in car on > >> > table file car > print sales > by country > by car > by model > end NUMBER OF RECORDS IN TABLE= 8 LINES= 8 ACCESS LIMITED BY FILTERS PAUSE.. PLEASE ISSUE CARRIAGE RETURN WHEN READY PAGE 1 COUNTRY CAR MODEL SALES ------- --- ----- ----- ENGLAND JAGUAR XJ12L AUTO 12000 FRANCE PEUGEOT 504 4 DOOR 0 ITALY ALFA ROMEO 2000 GT VELOCE 12400 2000 SPIDER VELOCE 13000 2000 4 DOOR BERLINA 4800 MASERATI DORA 2 DOOR 0 W GERMANY BMW 3.0 SI 4 DOOR 14000 3.0 SI 4 DOOR AUTO 18940 END OF REPORT

  18. FILTERS and FUNCTIONSInvisible FILTER > set filter = himargin in car off > > set filter = lomargin in car on > > table file car > print sales > by country > by car > by model > end NUMBER OF RECORDS IN TABLE= 3 LINES= 3 ACCESS LIMITED BY FILTERS PAUSE.. PLEASE ISSUE CARRIAGE RETURN WHEN READY PAGE 1 COUNTRY CAR MODEL SALES ------- --- ----- ----- W GERMANY BMW 2002 2 DOOR 8950 2002 2 DOOR AUTO 8900 530I 4 DOOR 14000 END OF REPORT

  19. FILTERS and FUNCTIONSInvisible FILTER Checking FILTER status ? FILTER [ {file | * } [ SET ] [ ALL ] ] With no parms ? FILTER * SET Show description and WHERE/IF definition for each declared filter Specify a single file or * for all files Show status of each declared filter

  20. FILTERS and FUNCTIONSInvisible FILTER Checking FILTER status > > ? FILTER CAR SET > > ? FILTER CAR SET SET FILE FILTER NAME DESCRIPTION --- -------- ----------- --------------------------------- CAR HIMARGIN MARGIN GREATER THAN 10% CAR MDMARGIN MARGIN BETWEEN 10 AND 20% * CAR LOMARGIN MARGIN BELOW 10% > Active Filters indicated with * Use DESC to differentiate FILTERs since Filter Name only 8 characters

  21. FILTERS and FUNCTIONSInvisible FILTER Checking FILTER status > > ? filter car all > > ? filter car all FILTER FILE CAR ADD PROFIT/D7=(RCOST-DCOST); MARGINPCT/D7.2=(PROFIT/DCOST)*100 ; NAME=HIMARGIN, DESC=MARGIN GREATER THAN 10% WHERE MARGINPCT GT 20.0 NAME=MDMARGIN, DESC=MARGIN BETWEEN 10 AND 20% WHERE MARGINPCT FROM 10.0 TO 20.0 NAME=LOMARGIN, DESC=MARGIN BELOW 10% WHERE MARGINPCT LT 10.0 END > > • Displays FILTER declarations. • Decrypts declarations for use and display. Be careful!

  22. FILTERS and FUNCTIONSInvisible FILTER FILTERs, DEFINEs and JOIN FILTER FILE CAR … DEFINE FILE CAR … TABLE FILE CAR Context 1 DEFINES and FILTERS added in Context 2 cleared by JOIN CLEAR JOIN COUNTRY IN CAR TO ALL COUNTRY IN SALES AS J1 TABLE FILE CAR Context 2 DEFINES and FILTERS From Context 1 cleared by JOIN

  23. FILTERS and FUNCTIONSInvisible FILTER FILTERs, DEFINEs and JOIN FILTER FILE CAR … DEFINE FILE CAR … TABLE FILE CAR Context 1 DEFINES and FILTERS added in Context 2 cleared by JOIN CLEAR SET KEEPFILTERS=ON, KEEPDEFINES=ON JOIN COUNTRY IN CAR TO ALL COUNTRY IN SALES AS J1 FILTER FILE CAR … DEFINE FILE CAR … TABLE FILE CAR Context 2 DEFINES and FILTERS from Context 1 retained!

  24. FILTERS and FUNCTIONSInvisible FILTER Questions on Filters?

  25. FILTERS and FUNCTIONSFunctions The Path to FUNCTIONS • Virtual fields are linked to Master File or Synonym • DEFINE • COMPUTE • RECAP • Often contain logic common to many fields • Reformating dates, names, times • Calculating standard comparison – margin, percent • Use DEFINE FUNCTION to make that logic reusable • DEFINEd FUNCTIONS - callable like IB functions • DEFINEd FUNCTIONS - independent of Master File

  26. FILTERS and FUNCTIONSFunctions The Uses of FUNCTIONS • Standardizing • Corporate business logic • FOCUS/WebFOCUS coding standards • Simplifying • Complicated groups of expressions • Repeated groups of expressions • Creating • New subroutines currently unavailable • Customization of existing functions • Based on the FOCUS/WebFOCUS language

  27. FILTERS and FUNCTIONSFunctions The Syntax of FUNCTIONS - 1 DEFINE FUNCTION name (argument1/format,…argumentn/formatn) [tempvariablea/formata = expressiona;] . . [tempvariablen/formatn = expressionn;] name/format = [result_expression]; END • Arguments and formats that are used when the function is called. • Fields actually used in call must match the type indicated – alpha or numeric. • Alpha • Too short – padded with spaces • Too long – truncated • Name of function. • Last field calculated in function. • Returns value to calling procedure.

  28. FILTERS and FUNCTIONSFunctions The Syntax of FUNCTIONS - 2 DEFINE FUNCTION name (argument1/format,…argumentn/formatn) [tempvariablea/formata = expressiona;] . . [tempvariablen/formatn = expressionn;] name/format = [result_expression]; END • Intermediate fields use in calculation of final result. • Unlimited number • Use argments, constants and other temporary fields declared in the function.

  29. FILTERS and FUNCTIONSFunctions The Syntax of FUNCTIONS - 3 DEFINE FUNCTION name (argument1/format,…argumentn/formatn) [tempvariablea/formata = expressiona;] . . [tempvariablen/formatn = expressionn;] name/format = [result_expression]; END • Final field defines the value returned. • Name must match name of function. • Format indicates the format returned.

  30. FILTERS and FUNCTIONSFunctions Using DEFINEd FUNCTIONS • DEFINE Functions can be called by other DEFINE Functions. • DEFINE FUNCTION Unlimits • Number of Functions used in session • Number of arguments used in a function. • DEFINE FUNCTION Limits • Can not call themselves. • Names must be 8 characters or less. • Argument names must be 12 characters or less.

  31. FILTERS and FUNCTIONSFunctions MKMARGIN Function DEFINE FUNCTION MKMARGIN CLEAR DEFINE FUNCTION MKMARGIN (RC/D7, DC/D7) NETPROFIT/D7=RC-DC; MKMARGIN/D5.2=(NETPROFIT*100)/DC; END TABLE FILE CAR PRINT COUNTRY CAR MODEL SALES AND COMPUTE MARGIN/D5.2 = MKMARGIN(RETAIL_COST,DEALER_COST); WHERE MKMARGIN(RETAIL_COST, DEALER_COST) GT 20 END

  32. FILTERS and FUNCTIONSFunctions MKMARGIN Function results PAGE 1 COUNTRY CAR MODEL SALES MARGIN ------- --- ----- ----- ------ ENGLAND JAGUAR XJ12L AUTO 12000 20.52 ITALY ALFA ROMEO 2000 GT VELOCE 12400 20.49 ITALY ALFA ROMEO 2000 SPIDER VELOCE 13000 20.49 ITALY ALFA ROMEO 2000 4 DOOR BERLINA 4800 20.55 ITALY MASERATI DORA 2 DOOR 0 26.00 W GERMANY BMW 3.0 SI 4 DOOR 14000 37.52 W GERMANY BMW 3.0 SI 4 DOOR AUTO 18940 28.39 FRANCE PEUGEOT 504 4 DOOR 0 21.14 END OF REPORT

  33. FILTERS and FUNCTIONSFunctions Displaying Active FUNCTIONS > ? FUNCTION > ? FUNCTION FUNCTIONS CURRENTLY ACTIVE Name Format Parameter Format -------- -------- ------------ -------- MKMARGIN D5.2 RC D7 DC D7 SUBTRACT D8.2 VAL1 D7 VAL2 D7

  34. FILTERS and FUNCTIONSFunctions Clearing FUNCTIONS DEFINE FUNCTION MKMARGIN CLEAR DEFINE FUNCTION MKMARGIN (RC/D7, DC/D7) NETPROFIT/D7=RC-DC; MKMARGIN/D5.2=(NETPROFIT*100)/DC; END TABLE FILE CAR PRINT COUNTRY CAR MODEL SALES AND COMPUTE MARGIN/D5.2 = MKMARGIN(RETAIL_COST,DEALER_COST); WHERE MKMARGIN(RETAIL_COST, DEALER_COST) GT 20 END DEFINE FUNCTION * CLEAR • Clear one specific function • Prevent collision of multiple functions • Allow dynamic function creation and use • Clear all active functions • Provides quick and easy cleanup

  35. FILTERS and FUNCTIONSFunctions STDNAME Function – alpha formatting DEFINE FUNCTION STDNAME (FN/A30, MI/A1, LN/A30) LNLOC/A30 = LCWORD( 30, LN, LNLOC ); FNLOC/A30 = LCWORD( 30, FN, FNLOC ); MIPER/A1=UPCASE( 1, MI, MIPER ) ; STDNAME/A65=LNLOC || (', ' | FNLOC) || ( ' ' | MIPER) || '.'; END DEFINE FUNCTION STDNAME (FN/A30, MI/A1, LN/A30) LNLOC/A30 = LCWORD( 30, LN, LNLOC ); FNLOC/A30 = LCWORD( 30, FN, FNLOC ); MIPER/A1=UPCASE( 1, MI, MIPER ) ; STDNAME/A65=LNLOC || (', ' | FNLOC) || ( ' ' | MIPER) || '.'; END TABLE FILE EMPDATA PRINT PIN AS EMPID AND COMPUTE NAME/A65 = STDNAME( FIRSTNAME, MIDINITIAL, LASTNAME ); END • Produces “Lastname, Firstname Middleinitial. “ • Oversized alpha parameters accommodate multiple field sizes • Parentheses to control spacing in final expression

  36. FILTERS and FUNCTIONSFunctions STDNAME Function results PAGE 1 EMPID NAME ----- ---- 000000010 Valino, Daniel A. 000000020 Bella, Michael D. 000000030 Cassanova, Lois E. 000000040 Adams, Ruth B. 000000050 Addams, Peter C. 000000060 Patel, Dorina K. 000000070 Sanchez, Evelyn P. 000000080 So, Pamela L. 000000090 Pulaski, Marianne D. 000000100 Anderson, Tim A.

  37. FILTERS and FUNCTIONSFunctions Other Candidates for Reformatting • Telephone Numbers • ID Numbers or SSNs • Product codes or SKUs • Address lines • Irregular line indentation • Any standardized alpha format

  38. FILTERS and FUNCTIONSFunctions EXPDATE Function – Time Value calculation DEFINE FUNCTION EXPDATE (LASTNAME/A15) CURTIM/HYYMDm = HGETC( 10, 'HYYMDm‘ ); LNAME/A15 = UPCASE( 15, LASTNAME, LNAME ); DEFINE FUNCTION EXPDATE (LASTNAME/A15) CURTIM/HYYMDm = HGETC( 10, 'HYYMDm‘ ); LNAME/A15 = UPCASE( 15, LASTNAME, LNAME ); EXPDATE/HYYMDm = IF ( EDIT(LNAME, '9‘ )) EQ 'A' OR 'B' OR 'C' OR 'D' OR 'E' THEN HADD( CURTIM, 'HOUR', &NUM, 10, 'HYYMDm') DEFINE FUNCTION EXPDATE (LASTNAME/A15) CURTIM/HYYMDm = HGETC( 10, 'HYYMDm‘ ); LNAME/A15 = UPCASE( 15, LASTNAME, LNAME ); EXPDATE/HYYMDm = IF ( EDIT(LNAME, '9‘ )) EQ 'A' OR 'B' OR 'C' OR 'D' OR 'E' THEN HADD( CURTIM, 'HOUR', &NUM, 10, 'HYYMDm') ELSE IF (EDIT( LNAME,'9‘ )) EQ 'F' OR 'G' OR 'H' OR 'I' OR 'J' THEN HADD( CURTIM, 'HOUR', &NUM+24 ,10, 'HYYMDm') ELSE IF (EDIT( LNAME,'9‘ )) EQ 'K' OR 'L' OR 'M' OR 'N' OR 'O' THEN HADD( CURTIM, 'HOUR', &NUM+48 ,10, 'HYYMDm') ELSE IF (EDIT( LNAME,'9‘ )) EQ 'P' OR 'Q' OR 'R' OR 'S' OR 'T' THEN HADD( CURTIM, 'HOUR', &NUM+72 ,10, 'HYYMDm') ELSE IF (EDIT( LNAME,'9‘ )) EQ 'U' OR 'V' OR 'W' OR 'X' OR 'Y' OR 'Z' THEN HADD( CURTIM, 'HOUR', &NUM+96 ,10, 'HYYMDm') ; END • Generates date-time field &NUM hours from current time • Combines multiple Datetime calls into single function • Parentheses to control spacing in final expression.

  39. FILTERS and FUNCTIONSFunctions EXPDATE Function DEFINE FILE EMPDATA CALLBACK/HYYMDm = EXPDATE (LASTNAME); END TABLE FILE EMPDATA PRINT LASTNAME BY CALLBACK END • Create BY field in DEFINE • Prompt for &NUM as runtime or include on EX line

  40. FILTERS and FUNCTIONSFunctions EXPDATE Function results PAGE 1 CALLBACK LASTNAME -------- -------- 2008/04/21 22:59:25.423078 BELLA 2008/04/21 22:59:25.423090 CASSANOVA 2008/04/21 22:59:25.423135 ADAMS 2008/04/21 22:59:25.423147 ADDAMS 2008/04/21 22:59:25.423343 ANDERSON 2008/04/21 22:59:25.423507 CVEK 2008/04/21 22:59:25.423828 DUBOIS 2008/04/21 22:59:25.423964 CONRAD 2008/04/21 22:59:25.424031 CASTALANETTA 2008/04/21 22:59:25.424262 DONATELLO

  41. FILTERS and FUNCTIONSFunctions Other Candidates for Selection • UserID – additional DBA • Location fields • Brand or Product fields • Product characteristic - color • Cost range or budget amount • Any field that could be used as a dimension

  42. FILTERS and FUNCTIONSFunctions ROLL4AVG Function – Rolling Average DEFINE FUNCTION ROLL4AVG (COND/A10, VAL/D12.2) CURR/D12.2 = VAL; L1CURR/D12.2 = IF COND EQ LAST COND THEN LAST CURR ELSE 0; L2CURR/D12.2 = IF COND EQ LAST COND THEN LAST L1CURR ELSE 0; L3CURR/D12.2 = IF COND EQ LAST COND THEN LAST L2CURR ELSE 0; L4/D12.2 = CURR + L1CURR + L2CURR + L3CURR; DEFINE FUNCTION ROLL4AVG (COND/A10, VAL/D12.2) CURR/D12.2 = VAL; L1CURR/D12.2 = IF COND EQ LAST COND THEN LAST CURR ELSE 0; L2CURR/D12.2 = IF COND EQ LAST COND THEN LAST L1CURR ELSE 0; L3CURR/D12.2 = IF COND EQ LAST COND THEN LAST L2CURR ELSE 0; L4/D12.2 = CURR + L1CURR + L2CURR + L3CURR; L4NUM/I1 = IF L1CURR EQ 0 THEN 1 ELSE IF L2CURR EQ 0 THEN 2 ELSE IF L3CURR EQ 0 THEN 3 ELSE 4; ROLL4AVG/D12.2 = L4 / L4NUM; END • Produces rolling average of last 4 values excluding zeroes • Single condition field used but could easily use multiple • LAST is supported here

  43. FILTERS and FUNCTIONSFunctions ROLL4AVG Function – Rolling Average TABLE FILE HEMPLOYEE PRINT GROSS AND COMPUTE ROLLAVG/D12.2=ROLL4AVG(EMP_ID,GROSS); BY EMP_ID BY PAY_DATE WHERE EMP_ID EQ ‘818692173’ END PAGE 1 EMP_ID PAY_DATE GROSS ROLLAVG ------ -------- ----- ------- 818692173 81/11/30 $2,147.75 2,147.75 81/12/31 $2,147.75 2,147.75 82/01/29 $2,147.75 2,147.75 82/02/26 $2,147.75 2,147.75 82/03/31 $2,147.75 2,147.75 82/04/30 $2,255.00 2,174.56 82/05/28 $2,255.00 2,201.38 82/06/30 $2,255.00 2,228.19 82/07/30 $2,255.00 2,255.00 82/08/31 $2,255.00 2,255.00 END OF REPORT

  44. FILTERS and FUNCTIONSFunctions Other Candidates for Creativity • Different Rounding • Trigonometric functions • Financial analysis • Accounting functions • Mathematical functions • Your in-house “business logic”

  45. FILTERS and FUNCTIONSFunctions Questions?

More Related