The Wonders of Field Derivation Language (FDL)

1. The Wonders ofField Derivation Language (FDL) Wednesday, May 18, 2005 2pm UK/Ireland, 3pm Central European, 9am Eastern US Thursday, May 19, 2005 9am Pacific, 12pm Eastern, 5pm UK/Ireland Please join the teleconference call now; if you have any difficulty, contact support@quadstone.com. Starting in 15 minutes Starting in 10 minutes Starting in 5 minutes Starting in 2 minutes Starting now

2. How to ask questions • Return to WebEx Event Manager: • Use Q&A (not Chat): • You can return to full-screen view:

3. The Wonders of Field Derivation Language (FDL) • Presenter: Eileen MacGregor, Support Manager • Overview: Field Derivation Language (FDL) is one of the most powerful features of the Quadstone System. It allows you to transform or combine fields to produce more useful data for analysis. However, the programmatic nature of FDL can be off-putting. This webinar aims to provide practical examples and useful tips for creating your own derived fields. • Audience: Anyone who wants to enhance their analysis with derived fields. • Format: A live demo, with slides for an introduction • Duration: 90 minutes

4. Overview • How do I derive a field? • Tips and guidelines • Working with numbers, strings, dates and fields in FDL • Practical applications • Flagging segments • Dealing with Nulls • Converting between data types • Adding reference information • Random selections • Matching patterns in text • Gotchas!

5. Colour coding in examples • FDL functions in red • Field names in blue • Literals (number/date/string) purple • All other FDL syntax in black

6. What do derivations do? • Most derivations manipulate existing data, but derivations do not need to refer to any field names — they can also use only functions: Age + 10; countwholeyears( DOB, today() ); if ( rndUniform() < 0.4 ) then "Y" else "N"; today();

7. FDL “laws” • You can do virtually anything in FDL • There is always more than one way to do everything • A good chance you’ll get it wrong first time — use the Help • ( ) and ; are your friends • You won’t break anything by trying

8. How to derive a field In Decisionhouse, derivations are done in the Table Viewer, by clicking on the New Field button:

9. How to derive a field By default, a single line derivation textbox appears:

10. How to derive a field — tip A multi-line textbox can be accessed by clicking on

11. How to derive a field — tip • All built-in functions can be accessed by clicking on

12. How to derive a field — tip • Function and field names can be entered partially, then completed using TAB-completion • Useful when there are lots of similarly-named fields

13. How to derive a field — tip • Use the Quadstone System Help, which can be accessed by pressing F1

14. Derivations — guidelines • Field names must be SQL-compliant (no reserved words and with only alphanumeric characters and the underscore _ character)

15. Derivations — guidelines • Working with fields, strings and dates • Fieldnames can be single-quoted or have no quotes: 'SpendInCents' / 100 is identical to SpendInCents / 100 • Auto-generated fields from DTB and SCB use single quotes

16. Derivations — guidelines • Working with fields, strings and dates • String values must be enclosed in double quotes Gender = "Male"

17. Derivations — guidelines • Working with fields, strings and dates • Date values must be preceded by a hash DOB < #01/01/1984 FirstTrans > #2000/06/30

18. Derivations — what if I get it wrong? If there is a mistake in the derivation, an error will be displayed:

19. Derivations — what if I get it wrong? If you derive a field, but use the wrong input fields/functions etc. • Either delete the field using and start again • Or simply correct the input FDL and press <Return>

20. Flagging a segment • To flag a segment of the population using a logical expression: if Gender = "F" and Income > 35000 then "Target" else "No Target"

21. Handling Nulls in data • Missing data (Nulls) can be transformed: if isnull(Response) then 0 else Response nvl(Response, 0)

22. Handling Nulls in data • Special functions handle Null data: minnonnull(CallsQ1, CallsQ2, CallsQ3) sumnonnull(Spend1, Spend2, Spend3)

23. Converting between types There are three datatypes for fields in the Quadstone System: • Numeric tointeger() / toreal() • String tostring() • Date todate()

24. Converting to numeric • Integer → Real • Real → Integer • Note anything after the decimal point will be truncated • Dates and suitable strings can be converted to numeric field types • Nulls will be generated for unsuitable strings

25. Converting to numeric • toreal(TotalTax) • tointeger(CurrentBalance) • tointeger(DateField) • tointeger( substr(StateCode, 2, 4) )

26. Converting to string • Numerics and dates can be converted to string values • Why convert to string? In order to use other string functions.

27. Converting to string tostring( today() ) concat(tostring(HouseNo), " ",StreetName)

28. Converting to string Example How do I convert a date field into a string field in the format “YYYY-MM” ?

29. Converting to string The answer initially given: M1 := month(Mydate); Yr := tostring(year(Mydate)); M2 :=if (M1 < 10) then concat("0", tostring(M1)) else tostring(M1); concat(Yr, "-", M2)

30. Converting to string The solution I came up with yesterday: x := tostring( Mydate ); concat( substr(x, 6, 9), "-", substr(x, 3, 4) )

31. Converting to date • Before the type-conversion functions, only individual numeric date components could be used to create a date field, using: • adddays, addmonths, addyears

32. Converting to date addyears(addmonths(adddays(#01/01/2000, MyDay - 1), MyMonth - 1), MyYear - 2000)

33. Converting to date • Much easier now • todate(20050515) — integer is in YMD format • todate("15/05/2005") — string is in ‘read’ format set in preferences, e.g. European, American • todate("15-May-2005", "%d-%b-%Y") —specified date format

34. Using lookup tables • Reference information can be added from lookup tables, using ‘key’ values in focus • Lookup tables generally small • Data does not have to be sorted

35. Using lookup tables

36. Using lookup tables I have a StateCode field in my focus, and I want to add the average income value per state. This data is held in another table

37. Using lookup tables Use the dblookup() function dblookup("focus:D:\Ref\USIncome.ftr", "US_State", "MeanIncome", substr(StateCode, 0, 1))

38. Selecting records randomly • Datasets can be split into segments, for example Test/Training: • numericTestTrainSplit() • Equal-sized populations: • sampleEqualSize() • Stratified sampling: • sampleStratified()

39. Selecting records randomly • To split the population into a 40% Test segment and a 60% Training segment: numericTestTrainSplit(0.4)

40. Selecting records randomly • To sample 2500 records from a population comprising females: sampleExactNumber(2500)

41. Selecting records randomly • To sample 2500 records from a segment containing 17219 records, where the segment comprises females, and 5000 records from the remaining population of 82781 records:

42. Selecting records randomly sampleStratified(2500, 17219, 5000, 82781, Gender = 2)

43. Regular expression matching • It is possible to ‘find’ patterns within string fields: • match() • And ‘replace’ patterns in strings: • replaceall() , replacefirst()

44. Regular expression matching • To look for StateCode values which begin with an alphabetic character: match("^[A-Z]" , StateCode )

45. Regular expression matching • To look for StateCode values which only contain alphabetic characters: match("^[A-Z]\+$" , StateCode )

46. Removing part of text in a string How can I remove the title information from a name field? replacefirst("^Mr & Mrs ", "", FullName)

47. Removing part of text in a string To handle all the different titles in one derivation: case match ("^Mr & Mrs ", FullName) : replacefirst("^Mr & Mrs ", "", FullName) ; match ("^Mrs ", FullName) : replacefirst("^Mrs ", "", FullName) ; match ("^Mr ", FullName) : replacefirst("^Mr ", "", FullName) ; …

48. Removing part of text in a string To handle all the different titles in one derivation: replacefirst( "^Mr & Mrs \|^Miss \|^Mrs \|^Ms \|^Mr \|^Rev \|^Dr " , "", FullName)

49. Regular expression matching One customer’s use: • To determine all possible entries for a product from a free-text field: match( "123", ProductCode )

50. Global variables • Global or accumulator variables allow values to be computed over entire sets of records For example, to create an index field: global i := 0; i := i + 1;