## Key

• This line was removed.
• Formatting was changed.
Excerpt

This example illustrates you to identify and list all values within a group that meet a specified condition.

Functions:

D generate list excerpts
pages ANYIF Function,LISTIF Function,WEEKDAY Function

Source:

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

EmployeeIdDateSales
S0011/23/1725
S0021/23/1740
S0031/23/1748
S0011/24/1781
S0021/24/1711
S0031/24/1725
S0011/25/179
S0021/25/1740
S0031/25/17
S0011/26/1777
S0021/26/1783
S0031/26/17
S0011/27/1717
S0021/27/1771
S0031/27/1729
S0011/28/17
S0021/28/17
S0031/28/1714
S0011/29/172
S0021/29/177
S0031/29/1799

Transformation:

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

D trans
RawWrangle true 'DayOfWeek' step derive type:single value:WEEKDAY(Date) as:'DayOfWeek' Formula type Single row formula Formula WEEKDAY(Date) New column name New formula

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

D trans
RawWrangle true 1 step pivot value:ANYIF(Sales, (Sales > 80 && DayOfWeek < 6)) group:EmployeeId,Date limit:1 Rows labels EmployeeId,Date Values ANYIF(Sales, (Sales > 80 && DayOfWeek < 6)) Max number of columns to create Pivot columns

Before adding the step to the recipe, you take note of the individuals who reached this mark in the `anyif_Sales` column for special 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:

D trans
RawWrangle true 1 step pivot value:LISTIF(Sales, 1000, (DayOfWeek < 6)) group:EmployeeId,Date limit:1 Rows labels EmployeeId,Date Values LISTIF(Sales, 1000, (DayOfWeek < 6)) Max number of columns to create Pivot columns

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

Code Block
```["
"]
[]```

Results:

EmployeeIdDatelistif_Sales
S0011/23/1725
S0021/23/1740
S0031/23/1748
S0011/24/1781
S0021/24/1711
S0031/24/1725
S0011/25/1740
S0021/25/17
S0031/25/1766
S0011/26/1777
S0021/26/1783
S0031/26/17
S0011/27/1717
S0021/27/1771
S0031/27/1729
S0011/28/17
S0021/28/17
S0031/28/17
S0011/29/17
S0021/29/17
S0031/29/17

D s also
label example_listif_functions