Date: Sun, 16 Jan 2022 09:32:13 +0000 (GMT) Message-ID: <2119171706.104066.1642325533966@9c5033e110b2> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_104065_36366291.1642325533965" ------=_Part_104065_36366291.1642325533965 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html EXAMPLE - PREV Function

EXAMPLE - PREV Function

This example describes how you can use the PREV function to analyze= data that is available in a window in rows before the current one.

Functions:

=20 =20 =20 =20 =20 =20 =20 =20 =20
Item Description
PREV Function Extracts the value from a column that is a spec= ified number of rows before the current value.
IF Function The IF function= allows you to build if/then/else conditional logic within your transforms.=

The following dataset contains orders for multiple customers over a peri= od of a few days, listed in no particular order. You want to assess how ord= er size has changed for each customer over time and to provide offers to yo= ur customers based on changes in order volume.

Source:

Date CustId OrderId OrderValue
1/4/16 C001 Ord002 500
1/11/16 C003 Ord005 200
1/20/16 C002 Ord007 300
1/21/16 C003 Ord008 400
1/4/16 C001 Ord001 100
1/7/16 C002 Ord003 600
1/8/16 C003 Ord004 700
1/21/16 C002 Ord009 200
1/15/16 C001 Ord006 900

Transformation:

When the data is loaded into the Transformer page, you can use the = PREV function to gather the order values for the previous= two orders into a new column. The trick is to order the window<= /code> transform by the date and group it by customer:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name = Window PREV(OrderValue, 1) CustId Date
=20

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name = Window PREV(OrderValue, 2) CustId Date
=20

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name Parameter: Column Parameter: Option=20 Manual rename window 'OrderValue_1'
=20

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name Parameter: Column Parameter: Option=20 Manual rename window1 'OrderValue_2'
=20

You should now have the following columns in your dataset: Da= teCustIdOrderId= OrderValueOrderValue_1OrderValue_2<= /code>.

The two new columns represent the previous order and the order before th= at, respectively. Now, each row contains the current order (OrderValu= e) as well as the previous orders. Now, you want to take the fo= llowing customer actions:

• If the current order is more than 20% greater than the sum of the two p= revious orders, send a rebate.
• If the current order is less than 90% of the sum of the two previous or= ders, send a coupon.
• Otherwise, send a holiday card.

To address the first one, you might add the following, which uses the IF function to test the value of the current order compared to t= he previous ones:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name <= code>New formula Single row formula IF(OrderValue >=3D (1.2 * (OrderValue_1= + OrderValue_2)), 'send rebate', 'no action') 'CustomerAction'
=20

You can now see which customers are due a rebate. Now, edit the above an= d replace it with the following, which addresses the second condition. If n= either condition is valid, then the result is send holiday card<= /code>.

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name <= code>New formula Single row formula IF(OrderValue >=3D (1.2 * (OrderValue_1= + OrderValue_2)), 'send rebate', IF(OrderValue <=3D (1.2 * (OrderValue_= 1 + OrderValue_2)), 'send coupon', 'send holiday card')) 'CustomerAction'
=20

Results:

After you delete the OrderValue_1 and = OrderValue_2 columns, your dataset should look like the followi= ng. Since the transformations with PREV functions gr= ouped by CustId, the order of records has changed.

Date CustId OrderId OrderValue CustomerAction
1/4/16 C001 Ord001 100 send rebate
1/7/16 C001 Ord002 500 send rebate
1/15/16 C001 Ord006 900 send rebate
1/8/16 C003 Ord004 700 send rebate
1/11/16 C003 Ord005 200 send rebate
1/21/16 C003 Ord008 400 send coupon
1/7/16 C002 Ord003 600 send rebate
1/20/16 C002 Ord007 300 send rebate
1/21/16 C002 Ord009 200 send coupon

=20
=20