Date: Mon, 6 Dec 2021 03:02:58 +0000 (GMT) Message-ID: <238697487.87221.1638759778196@9c5033e110b2> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_87220_472379001.1638759778195" ------=_Part_87220_472379001.1638759778195 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html EXAMPLE - LISTIF Functions

# EXAMPLE - LISTIF Functions

This section provides simple examples for how to use the ```A= NYIF``` and `LISTIF` functions. These functions in= clude the following:

• `ANYIF` - Identifies a single value from a group that m= eets a specific condition. See ANYIF= Function.
• `LISTAIF` - Lists all values within a group that meet a= specified condition. See LISTIF Fu= nction

Source:

The following data identifies sales figures by salespeople for a week:

EmployeeId Date Sales
S001 1/23/17 25
S002 1/23/17 40
S003 1/23/17 48
S001 1/24/17 81
S002 1/24/17 11
S003 1/24/17 25
S001 1/25/17 9
S002 1/25/17 40
S003 1/25/17
S001 1/26/17 77
S002 1/26/17 83
S003 1/26/17
S001 1/27/17 17
S002 1/27/17 71
S003 1/27/17 29
S001 1/28/17
S002 1/28/17
S003 1/28/17 14
S001 1/29/17 2
S002 1/29/17 7
S003 1/29/17 99

Transformation:

In this example, you are interested in the high performers. A good day i= n sales is one in which an individual sells more than 80 units. First, you = want to identify the day of week:

=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` `WEEKDAY(Date)` `'DayOfWeek'`
=20

Values greater than 5 in `DayOfWeek` are weekend dates. = You can use the following to identify if anyone reached this highwater mark= er during the workweek (non-weekend):

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 = =20 =20 =20
Transformation Name `Pivot columns` `EmployeeId,Date` ```ANYIF(Sales, (Sales > 80 && Day= OfWeek < 6))``` `1`
=20

Before adding the step to the recipe, you take note of the individuals w= ho reached this mark in the `anyif_Sales` column for specia= l recognition.

Now, you want to find out sales for individuals during the week. You can= use the following to filter the data to show only for weekdays:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 = =20 =20 =20
Transformation Name `Pivot columns` `EmployeeId,Date` `LISTIF(Sales, 1000, (DayOfWeek < 6))` `1`
=20

To clean up, you might select and replace the following values in the li= stif_Sales column with empty strings:

=20
```["
"]
[]```
=20

Results:

EmployeeId Date listif_Sales
S001 1/23/17 25
S002 1/23/17 40
S003 1/23/17 48
S001 1/24/17 81
S002 1/24/17 11
S003 1/24/17 25
S001 1/25/17 40
S002 1/25/17
S003 1/25/17 66
S001 1/26/17 77
S002 1/26/17 83
S003 1/26/17
S001 1/27/17 17
S002 1/27/17 71
S003 1/27/17 29
S001 1/28/17
S002 1/28/17
S003 1/28/17
S001 1/29/17
S002 1/29/17
S003 1/29/17

------=_Part_87220_472379001.1638759778195--