Contents:
Function:
| Item | Description |
|---|---|
| DATEDIF Function | Calculates the difference between two valid date values for the specified units of measure. |
| TODAY Function | Derives the value for the current date in UTC time zone. You can specify a different time zone by optional parameter. |
| IF Function |
The IF function allows you to build if/then/else conditional logic within your transforms.
|
Source:
For the orders in the following set, you want to charge interest for those ones that are older than 90 days.
| OrderId | OrderDate | Amount |
|---|---|---|
| 1001 | 1/31/16 | 1000 |
| 1002 | 11/15/15 | 1000 |
| 1003 | 12/18/15 | 1000 |
| 1004 | 1/15/16 | 1000 |
Transformation:
The first step is to create a column containing today's (03/03/16) date value:
| Transformation Name | New formula |
|---|---|
| Parameter: Formula type | Single row formula |
| Parameter: Formula | TODAY() |
| Parameter: New column name | 'Today' |
You can now use this value as the basis for computing the number of elapsed days for each invoice:
| Transformation Name | New formula |
|---|---|
| Parameter: Formula type | Single row formula |
| Parameter: Formula | DATEDIF(OrderDate, Today, day) |
The age of each invoice in days is displayed in the new column. Now, you want to add a little bit of information to this comparison. Instead of just calculating the number of days, you could write out the action to undertake. Replace the above with the following:
| Transformation Name | New formula |
|---|---|
| Parameter: Formula type | Single row formula |
| Parameter: Formula | IF((DATEDIF(OrderDate, Today, day) > 90),'Charge interest','no action') |
| Parameter: New column name | 'TakeAction' |
To be fair to your customers, you might want to issue a notice at 45 days that the invoice is outstanding. You can replace the above with the following:
| Transformation Name | New formula |
|---|---|
| Parameter: Formula type | Single row formula |
| Parameter: Formula | IF(DATEDIF(OrderDate, Today, day) > 90,'Charge interest',IF(DATEDIF(OrderDate, Today, day) > 45),'Send letter','no action')) |
| Parameter: New column name | 'TakeAction' |
By using nested instances of the IF function, you can generate multiple results in the TakeAction column.
For the items that are over 90 days old, you want to charge 5% interest. You can do the following:
| Transformation Name | Edit column with formula |
|---|---|
| Parameter: Columns | Amount |
| Parameter: Formula | IF(TakeAction == 'Charge interest',Amount * 1.05,Amount) |
The above sets the value in the Amount column based on the conditional of whether the TakeAction column value is Charge interest. If so, apply 5% interest to the value in the Amount column.
Results:
| OrderId | OrderDate | Amount | Today | TakeAction |
|---|---|---|---|---|
| 1001 | 1/31/16 | 1000 | 03/03/16 | no action |
| 1002 | 11/15/15 | 1050 | 03/03/16 | Charge interest |
| 1003 | 12/18/15 | 1000 | 03/03/16 | Send letter |
| 1004 | 1/15/16 | 1000 | 03/03/16 | Send letter |
This page has no comments.