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 r0681

This example illustrates how various type checking functions can be applied to your data.

Source:

Some source values that should match the State and Integer data types:

StateQty
CA10
OR-10
WA2.5
ZZ15
ID 
 4

Transformation:

You can test for invalid values for State using the following:

D trans
RawWrangletrue
Typestep
WrangleTextderive type:single value: ISMISMATCHED (State, 'State')
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueISMISMATCHED (State, 'State')
SearchTermNew formula

You can test for valid matches for Qty using the following:

D trans
RawWrangletrue
p03Value'valid_Qty'
Typestep
WrangleTextderive type:single value: (ISVALID (Qty, 'Integer') && (Qty > 0)) as:'valid_Qty'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Value(ISVALID (Qty, 'Integer') && (Qty > 0))
p03NameNew column name
SearchTermNew formula

The first transform flags rows 4 and 6 as mismatched.

Info

NOTE: A missing value is not valid for a type, including String type.

The second transform flags as valid all rows where the Qty column is a valid integer that is greater than zero.

The following transform tests for the presence of missing values in either column:

D trans
RawWrangletrue
p03Value'missing_State_Qty'
Typestep
WrangleTextderive type:single value: (ISMISSING(State) || ISMISSING(Qty)) as:'missing_State_Qty'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Value(ISMISSING(State) || ISMISSING(Qty))
p03NameNew column name
SearchTermNew formula

After re-organizing the columns using the move transform, the dataset should now look like the following:

StateQtymismatched_Statevalid_Qtymissing_State_Qty
CA10falsetruefalse
OR-10falsefalsefalse
WA2.5falsefalsefalse
ZZ15truetruefalse
ID falsefalsetrue
 4falsetruetrue

Since the data does not contain null values, the following transform generates null values based on the preceding criteria:

D trans
RawWrangletrue
p03Value'status'
Typestep
WrangleTextderive type:single value: ((mismatched_State == 'true') || (valid_Qty == 'false') || (missing_State_Qty == 'true')) ? NULL() : 'ok' as:'status'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Value((mismatched_State == 'true') || (valid_Qty == 'false') || (missing_State_Qty == 'true')) ? NULL() : 'ok'
p03NameNew column name
SearchTermNew formula

You can then use the ISNULL check to remove the rows that fail the above test:

D trans
RawWrangletrue
p03ValueISNULL('status')
Typestep
WrangleTextdelete row: ISNULL('status')
p01NameCondition
p01ValueCustom formula
p02NameType of formula
p02ValueCustom single
p03NameCondition
p04ValueDelete matching rows
p04NameAction
SearchTermFilter rows

Results:

Based on the above tests, the output dataset contains one row:

StateQtymismatched_Statevalid_Qtymissing_State_Qtystatus
CA10falsetruefalseok