Date: Tue, 21 Mar 2023 10:51:54 +0000 (UTC) Message-ID: <1707692231.79331.1679395914154@1323b43bed1d> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_79330_572749292.1679395914154" ------=_Part_79330_572749292.1679395914154 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html EXAMPLE - LIST and UNIQUE Function

# EXAMPLE - LIST and UNIQUE Function

This example illustrates the following functions:

• `LIST` - Extracts up to 1000 values from one column into an = array in a new column. See LIS= T Function.
• `UNIQUE` - Extracts up to 1000 unique values from one column= into an array in a new column. See UNIQUE Function.

You have the following set of orders for two months, and you are interes= ted in identifying the set of colors that have been sold for each product f= or each month and the total quantity of product sold for each month. <= /p>

Source:

OrderId Date Item Qty Color
1001 1/15/15 Pants 1 red
1002 1/15/15 Shirt 2 green
1003 1/15/15 Hat 3 blue
1004 1/16/15 Shirt 4 yellow
1005 1/16/15 Hat 5 red
1006 1/20/15 Pants 6 green
1007 1/15/15 Hat 7 blue
1008 4/15/15 Shirt 8 yellow
1009 4/15/15 Shoes 9 brown
1010 4/16/15 Pants 1 red
1011 4/16/15 Hat 2 green
1012 4/16/15 Shirt 3 blue
1013 4/20/15 Shoes 4 black
1014 4/20/15 Hat 5 blue
1015 4/20/15 Pants 6 black

Transformation:

To track by month, you need a column containing the month value extracte= d from the date:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 = =20 =20
Transformation Name `Edit column with formula` `Date` `DATEFORMAT(Date, 'MMM yyyy')`
=20

You can use the following transform to check the list of unique values a= mong the colors:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 = =20 =20 =20
Transformation Name `Pivot columns` `Date` `unique(Color, 1000)` `10`
=20

Date unique_Color
Jan 2015 ["green","blue","red","yellow"]
Apr 2015
["brown","blue","red","yellow","black","green"]

Delete the above transform.

You can aggregate the data in your dataset, grouped by the reformatted&n= bsp;`Date` values, and apply the `LIST`&nbs= p;function to the `Color` column. In the same aggregat= ion, you can include a summation function for the `Qty`&nbs= p;column:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 = =20 =20 =20
Transformation Name `Pivot columns` `Date` `list(Color, 1000),sum(Qty)` `10`
=20

Results:

Date list_Color sum_Qty
Jan 2015 ["green","blue","blue","red","green","red","yell= ow"] 28
Apr 2015
["brown","blue","red","yellow","black","blue","black","green"]
38

------=_Part_79330_572749292.1679395914154--