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

...

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

 

TransformTransformation:

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

Info

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

d-

...

trans
RawWrangletrue
p03Value(ProdId == 'P100')
Typestep
WrangleTextdelete row:(ProdId == 'P100')
p01NameCondition
p01ValueCustom formula
p02NameType of formula
p02ValueCustom single
p03NameCondition
p04ValueDelete matching rows
p04NameAction
SearchTermFilter rows

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 transformation in the following expansion of the transformtransformation, in which any row that has a matching ProdId value is removed:

d-

...

trans
RawWrangletrue
p03Value(ProdId == 'P100' || ProdId == 'P101' || ProdId == 'P102' || ProdId == 'P103')
Typestep
WrangleTextdelete row:(ProdId == 'P100' || ProdId == 'P101' || ProdId == 'P102' || ProdId == 'P103')
p01NameCondition
p01ValueCustom formula
p02NameType of formula
p02ValueCustom single
p03NameCondition
p04ValueDelete matching rows
p04NameAction
SearchTermFilter rows

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

...

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

d-

...

trans
RawWrangletrue
p03Value'today'
Typestep
WrangleTextderive value:'2/25/16' as:'today'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Value'2/25/16'
p03NameNew column name
SearchTermNew formula

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

d-

...

trans
RawWrangletrue
p03Valuedatedif(OrderDate,today,day) <= 90
Typestep
WrangleTextkeep row:

...

datedif(OrderDate,today,day) <= 90
p01NameCondition
p01ValueCustom formula
p02NameType of formula
p02ValueCustom single
p03NameCondition
p04ValueKeep matching rows
p04NameAction
SearchTermFilter rows

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

d-

...

trans
RawWrangletrue
Typestep
WrangleTextdrop col:today
p01NameColumns
p01Valuetoday
p02NameAction
p02ValueDelete selected columns
SearchTermDelete columns

Results:

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