Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space DEV and version next

...

Column reference example:

RIGHT
d-codelang-syntax
RawWrangletrue
Typeref
showNotetrue
WrangleTextderive type:single value:
right(MyString,3)

right(MyString,3)

Output:

...

Returns the rightmost (last) three letters of the MyName column value

...

String literal example:

RIGHT
d-codelang-syntax
RawWrangletrue
Typeref
showNotetrue
WrangleTextderive type:single value:
right('Hello, World',5)

...

right('Hello, World',5)

Output: Returns the string: World.

D s
snippetSyntax

RIGHT
d-codelang-syntax
RawWrangletrue
Typesyntax
showNotetrue
WrangleTextderive type:single value:
right(column_string,end_count)

right(column_string,end_count)


ArgumentRequired?Data TypeDescription
column_stringYstringName of the column or string literal to be applied to the function
end_countYinteger (positive)Count of characters from the end of the source string to apply to the match

...

ParticipantIdSocialNum
1001805-88-2013
1002845221914
1003865 22 9291
1004892-732213

TransformTransformation:

When the above data is imported, the SocialNum column might or might not be inferred as SSN data type. Either way, you should clean up your data, using the following transforms:

d-

...

trans
RawWrangletrue
p03Value''
Typestep
WrangleTextreplace col: SocialNum on: '-' with: '' global: true

...

p01NameColumn
p01ValueSocialNum
p02NameFind
p02Value'-'
p03NameReplace with
p04Valuetrue
p04NameMatch all occurrences
SearchTermReplace text or pattern

D trans
RawWrangletrue
p03Value''
Typestep
WrangleTextreplace col: SocialNum on: ' ' with: '' global: true
p01NameColumn
p01ValueSocialNum
p02NameFind
p02Value' '
p03NameReplace with
p04Valuetrue
p04NameMatch all occurrences
SearchTermReplace text or pattern

At this point, your SocialNum data should be inferred as SSN type and consistently formatted as a set of digits:

ParticipantIdSocialNum
1001805882013
1002845221914
1003865229291
1004892732213

From this more consistent data, you can now break out the area, group, and serial values from the column:

d-

...

trans
RawWrangletrue
p03Value'SSN_area'
Typestep
WrangleTextderive type:single value:

...

left(SocialNum, 3) as: 'SSN_area'

...

p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Valueleft(SocialNum, 3)
p03NameNew column name
SearchTermNew formula

D trans
RawWrangletrue
p03Value'SSN_group'
Typestep
WrangleTextderive type:single value:

...

substring(SocialNum, 3,5) as: 'SSN_group'

...

p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Valuesubstring(SocialNum, 3,5)
p03NameNew column name
SearchTermNew formula

D trans
RawWrangletrue
p03Value'SSN_serial'
Typestep
WrangleTextderive type:single value:

...

right(SocialNum, 4) as: 'SSN_serial'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Valueright(SocialNum, 4)
p03NameNew column name
SearchTermNew formula

If desired, you can re-order the three new columns and delete the source column:

d-

...

trans
RawWrangletrue
p03ValueSSN_area
Typestep
WrangleTextmove col: SSN_serial after: SSN_area

...

p01NameColumn(s)
p01ValueSSN_serial
p02NameOption
p02ValueAfter
p03NameColumn
SearchTermMove columns

D trans
RawWrangletrue
p03ValueSSN_area
Typestep
WrangleTextmove col: SSN_group after: SSN_area

...

p01NameColumn(s)
p01ValueSSN_group
p02NameOption
p02ValueAfter
p03NameColumn
SearchTermMove columns

D trans
RawWrangletrue
Typestep
WrangleTextdrop col:SocialNum
p01NameColumns
p01ValueSocialNum
p02NameAction
p02ValueDelete selected columns
SearchTermDelete columns

Results:

If you complete the previous transform steps, your data should look like the following:

...