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	




​

 

(1) Answer

Login