Calculated column based on column value in spotfire

Hi All,

I'm new to Spotfire and I'm trying to create a calculated column called WVAL which is simple enough; however, it gets complicated because the value of the calculated column must be based on the value of the test W within each group.  The value of W must only populate tests L, M, N, B, E.

The logic is as follows:

1. Group data by CONTAINER ID.

2. After grouping by CONTAINER ID, get the value for the test W.

3. Apply that value for the test W to calulated column WVAL for only tests L, M, N, B, E.

I'm not sure what functions I should use for the expression.  Any guidance you can provide would be appreciated.

The data looks like this:

``````​
The data looks like this.
ID	VISIT	DATE	        TIME	CONTAINER ID	TEST	VALUE
1	A	1-Jan-17	080000	1111	        L	0.4
1	A	1-Jan-17	080000	1111	        M	4.2
1	A	1-Jan-17	080000	1111	        N	13.7
1	A	1-Jan-17	080000	1111	        B	9.1
1	A	1-Jan-17	080000	1111	        E	72
1	A	1-Jan-17	080000	1111	        W	4.81
2	A	15-Jul-17	101500	1112	        L	0.3
2	A	15-Jul-17	101500	1112	        M	1.5
2	A	15-Jul-17	101500	1112	        N	11
2	A	15-Jul-17	101500	1112	        B	7.2
2	A	15-Jul-17	101500	1112	        E	83
2	A	15-Jul-17	101500	1112	        W	5.23

After the calculated column (WVAL) has been created, the data should look like this.
ID	VISIT	DATE	        TIME	CONTAINER ID	TEST	VALUE	WVAL
1	A	1-Jan-17	080000	1111	        L	0.4	4.81
1	A	1-Jan-17	080000	1111	        M	4.2	4.81
1	A	1-Jan-17	080000	1111	        N	13.7	4.81
1	A	1-Jan-17	080000	1111	        B	9.1	4.81
1	A	1-Jan-17	080000	1111	        E	72	4.81
1	A	1-Jan-17	080000	1111	        W	4.81
2	A	15-Jul-17	101500	1112	        L	0.3	5.23
2	A	15-Jul-17	101500	1112	        M	1.5	5.23
2	A	15-Jul-17	101500	1112	        N	11	5.23
2	A	15-Jul-17	101500	1112	        B	7.2	5.23
2	A	15-Jul-17	101500	1112	        E	83	5.23
2	A	15-Jul-17	101500	1112	        W	5.23

​``````