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

HSS — Fri, 09 Jun 2023 18:08:15 GMT

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.

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

frank_wang — Thu, 06 Jan 2022 00:31:51 GMT

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",
	""
);

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

HSS — Fri, 07 Jan 2022 10:09:08 GMT

Thanks Frank.

It worked.

topic Add one more column based on condition and grouping on other columns in Discussions

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

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

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