Add one more column based on condition and grouping on other columns

[#1]

Hello All,
Happy new year, 
I stuck at another place, have idea/logic but not able to put into correct formula. Please see the attached image for description. Sample data is also attached.

Any help?
Many thanks.

[#2 · Accepted solution]

Hi HSS

I think there are 3 steps need to do to get your want.

1.  Summarize your data table to map. Index is 'sensor' and Values are 'max(Inlet), max(Reture)'. 

//You can use below JSL to summarzie a new data table

Data Table( "Data" ) << Summary(
	Group( :Sensor ),
	Max( :Occurrence ),
	Subgroup( :Name( "Sub-Sensor" ) ),
	Freq( "None" ),
	Weight( "None" ),
	statistics column name format( "column" )
);

2. Input your conditions by if or match. Here I used If and create a new column in summary data table.

If(
	:Name( "Occurrence, Inlet" ) >= 2 & :Name( "Occurrence, Return" ) >= 2, "Both",
	:Name( "Occurrence, Inlet" ) >= 2 & :Name( "Occurrence, Return" ) < 2, "Inlet",
	:Name( "Occurrence, Inlet" ) < 2 & :Name( "Occurrence, Return" ) >= 2, "Return",
	"Empty"
);

3. Join two data table together by 'Sensor' and make sure column' result ' include. Then use condition if to make it real.

// Join summary data table to original data table

Data Table( "Data" ) << Join(
	With( Data Table( "Data By (Sensor)" ) ),
	Select(
		:ID,
		:Sensor,
		:Name( "Sub-Sensor" ),
		:Occurrence,
		:Required Column,
		:Name( "Required Column -Cause" )
	),
	SelectWith( :Result ),
	By Matching Columns( :Sensor = :Sensor ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 0, 0 ),
	Preserve main table order( 1 )
);

// add formula in column 'Required Column -Cause' as below

If(
	:Name( "Sub-Sensor" ) == "Inlet" & :Result == "Inlet", "Inlet",
	:Name( "Sub-Sensor" ) == "Return" & :Result == "Return", "Return",
	:Name( "Sub-Sensor" ) == "Inlet" | :Name( "Sub-Sensor" ) == "Return" & :Result == "Both", "Both",
	""
);

[#3]

Thanks Frank.

It worked.