Uplift Modeling

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_at_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 QA (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() lt 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 youll get it wrong first time
  use the Help
• ( ) and are your friends
• You wont 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 lt 01/01/1984
• FirstTrans gt 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 ltReturngt

20
Flagging a segment

• To flag a segment of the population using a
  logical expression
• if Gender "F" and Income gt 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 lt 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("focusD\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 customers 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

51
Global variables

• The most common use by customers is to
  de-duplicate a dataset

52
Global variables

• The most common use by customers is to
  de-duplicate a dataset

53
Global variables

• global lastID ""
• flag1st (CustID ! lastID)
• lastID CustID
• flag1st

54
Derivations gotchas

• Derived fields are created in the focus currently
  being used (so beware if subfoci exist they
  dont propagate)

55
Derivations gotchas

• Beware of rederiving fields with different
  derivation text which may cause the output data
  type to change
• The type of a derived field is fixed
• You wont get what you expect

56
Derivations gotchas

• Results are generated for all records in the
  focus, irrespective of current selections
• isselected()

57
Derivations gotchas

• Field names are case-sensitive

58
Where to find out more

• Quadstone System Help
• Field Derivation Language (FDL) reference
• Syntax and examples
• Quadstone System Support website
  http//support.quadstone.com/

59
Questions and answers
60
After the webinar

• These slides are available via www.quadstone.com/t
  raining/webinars/
• Any problems or questions, please contact
  support_at_quadstone.com

61
Upcoming webinars
What's new in the Quadstone System Version 5.1? June 23, 2005 9am Pacific 11am Central 12 noon Eastern
What's new in the Quadstone System Version 5.1? June 24, 2005 1400 UK/Ireland 1500 Central European
Getting Started with the Quadstone System July 27, 2005 9am Pacific 11am Central 12 noon Eastern
Getting Started with the Quadstone System July 28, 2005 1400 UK/Ireland 1500 Central European

• See www.quadstone.com/training/webinars/.
• If theres a webinar topic youd like to see,
  please let us know via support_at_quadstone.com.

62
Your feedback
Please email support_at_quadstone.com