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

...

You can generalize this formatting across multiple columns by applying the $col reference in the transform's function, as in the following:

d-

...

trans
Typestep
p01NameColumns
p01ValuecolA, colB, colC

...

p02NameFormula
p02ValueIFVALID($col, ['Float'], ROUND($col, 2))
SearchTermEdit column with formula

See Column Details Panel

...

  1. In the data grid, select an instance of " kg". Note that the space should be selected, too.
  2. Among the suggestion cards, select the Replace card. 
  3. It should automatically choose to replace with nothing, effectively deleting the content. To check, click Modify
  4. The transform should look like the following:

    d-

    code
    hideNotetrue

    replace col: Weight_kg on: ` kg` with: '' global: true

    trans
    p03Value''
    Typestep
    p01NameColumn
    p01ValueWeight_kg
    p02NameFind
    p02Value' kg'
    p03NameReplace with
    p04Valuetrue
    p04NameMatch all occurrences
    SearchTermReplace text or patterns

  5. Add it to your recipe.
  6. Verify that the column's data type has been changed to Integer or Decimal, depending on the values in it.

...

To normalize to English units, the first issue is easily corrected by multiplying the Weight values by 2.2, since 1 kg = 2.2 lb:

d-

...

hideNotetrue

...

trans
Typestep
p01NameColumns
p01ValueWeight_kg
p02NameFormula
p02Value(Weight_kg * 2.2)
SearchTermEdit column with formula

If you want to round the value to the nearest integer, use the following:

d-

...

hideNotetrue

...

trans
Typestep
p01NameColumns
p01ValueWeight_kg
p02NameFormula
p02ValueROUND((Weight_kg * 2.2))
SearchTermEdit column with formula

After the above is added to the recipe, you should rename the column: Weight_lbs.

...

  1. Select the first quote mark in one of the entries. 
  2. In the suggestion cards, select the Replace card.
  3. Select the variant that deletes all quotes in the column. 

  4. The full command should look like the following:

    d-

    code
    hideNotetrue

    replace col: Height_ft on: `"` with: '' global: true

    trans
    p03Value''
    Typestep
    p01NameColumn
    p01ValueHeight_ft
    p02NameFind
    p02Value`"`
    p03NameReplace with
    p04Valuetrue
    p04NameMatch all occurrences
    SearchTermReplace text or patterns

  5. Add it to your recipe.
  6. The remaining steps compute the number of inches. Multiply the feet by 12, and then add the number of inches, using new columns of data.
  7. Select the single quote mark, and choose the Split suggestion card. This transform step should split the column into two columns: Height_ft1 and Height_ft2
  8. Derive the value in inches:

    d-

    code
    hideNotetrue
    derive type:single value:Rename the new column: Height_in.

    trans
    p03ValueHeight_in
    Typestep
    p01NameFormula type
    p01ValueSingle row formula
    p02NameFormula
    p02Value((Height_ft1 *12)+Height_ft2)

  9. Add it to your recipe.
  10. p03NameNew column name
    SearchTermNew formula

  11. You can drop the other, interim columns.

...

If you have inconsistent units within a column, it might be possible to correct these values by applying a multiple. For example, you might be able to determine that some values are in kilometers, instead of meters, based on their much smaller values. Multiplying the kilometer values by 1000 should standardize your units. The following multiplies all values in the column Distance that are less than 1000 by 1000. 

d-

...

hideNotetrue

...

trans
Typestep
p01NameColumns
p01ValueDistance
p02NameFormula
p02ValueIF((Distance < 1000

...

,(Distance * 1000)

...

, Distance)
SearchTermEdit column with formula

Note the implied assumption that there are no distances in kilometers that are over 1000. 

...

You can use the following transforms to perform rounding functions within these columns:

d-

...

hideNotetrue

...

trans
Typestep
p01NameColumns
p01ValueWidth_cm
p02NameFormula
p02ValueNUMFORMAT(Width_cm '#.#')
SearchTermEdit column with formula

d-

...

hideNotetrue

...

trans
Typestep
p01NameColumns
p01ValueHeight_cm
p02NameFormula
p02ValueNUMFORMAT(Height_cm '#.#')
SearchTermEdit column with formula

Info

NOTE: The above assumes that the number of significant digits remains fixed in the source data. If this varies over times or uses of the transform recipe, you might need to revisit these specific transform steps.

...