Date: Tue, 18 Jan 2022 07:17:48 +0000 (GMT) Message-ID: <916705232.105440.1642490268819@9c5033e110b2> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_105439_2074008867.1642490268819" ------=_Part_105439_2074008867.1642490268819 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_105439_2074008867.1642490268819--