EXAMPLE - Delete and Keep Transforms

This examples illustrates how you can keep and delete rows from your dataset using the following transforms:

  • delete - Deletes a set of rows as evaluated by the conditional expression in the row parameter. See Delete Transform.
  • keep - Retains a set of rows as evaluated by the conditional expression in the row parameter. All other rows are deleted from the dataset. See Keep Transform.

Source:

Your dataset includes the following order information. You want to edit your dataset so that:

  • All orders for products that are no longer available are removed. These include the following product IDs: P100, P101, P102, P103.
  • All orders that were placed within the last 90 days are retained.
OrderIdOrderDateProdIdProductNameProductColorQtyOrderValue
10016/14/2015P100HatBrown190
10021/15/2016P101HatBlack2180
100311/11/2015P103SweaterBlack3255
10048/6/2015P105CardiganRed4320
10057/29/2015P103SweeterBlack5375
100612/1/2015P102PantsWhite6420
100712/28/2015P107T-shirtWhite7390
10081/15/2016P105CardiganRed8420
10091/31/2016P108CoatNavy9495

Transform:

First, you remove the orders for old products. Since the set of products is relatively small, you can start first by adding the following:

NOTE: Just preview this transform. Do not add it to your recipe yet.

delete row:(ProdId == 'P100')

When this step is previewed, you should notice that the top row in the above table is highlighted for removal. Notice how the transform relies on the ProdId value. If you look at the ProductName value, you might notice that there is a misspelling in one of the affected rows, so that column is not a good one for comparison purposes.

You can add the other product IDs to the transform in the following expansion of the transform, in which any row that has a matching ProdId value is removed:

delete row:(ProdId == 'P100' || ProdId == 'P101' || ProdId == 'P102' || ProdId == 'P103')

When the above step is added to your recipe, you should see data that looks like the following:

OrderIdOrderDateProdIdProductNameProductColorQtyOrderValue
10048/6/2015P105CardiganRed4320
100712/28/2015P107T-shirtWhite7390
10081/15/2016P105CardiganRed8420
10091/31/2016P108CoatNavy9495

Now, you can filter out of the dataset orders that are older than 90 days. First, add a column with today's date:

derive value:'2/25/16' as:'today'

Keep the rows that are within 90 days of this date using the following:

keep row:DATEDIF(OrderDate,today,day) <= 90

Don't forget to drop the today column, which is no longer needed:

drop col:today

Results:

OrderIdOrderDateProdIdProductNameProductColorQtyOrderValue
100712/28/2015P107T-shirtWhite7390
10081/15/2016P105CardiganRed8420
10091/31/2016P108CoatNavy9495
Was this page helpful? Let us know how we did:

Send feedback about...

Google Cloud Dataprep Documentation