How to get non-empty row values corresponding to chosen labels from columns?I have incoming data of the form as attached.
I want to get the first first three row values from each column (encircled red) which appear against specific labels.
The following works for Current but not for Voltage (for Voltage I get empty values which I understand why)
Stage = "7"; resCol = "Current";
theLSL = Column( dt, resCol )[(dt << get rows where( :TestStage == Stage & :label == "LSL" ))[1]]; show (theLSL);
theUSL = Column( dt, resCol )[(dt << get rows where( :TestStage == Stage & :label == "USL" ))[1]]; show (theUSL);
Automation and ScriptingWindowsRe: How to get non-empty row values corresponding to chosen labels from columns?You could add additional check using IsMissing()
... & !IsMissing(Column(dt, resCol)[])
Re: How to get non-empty row values corresponding to chosen labels from columns?@jthi Thanks.
Re: How to get non-empty row values corresponding to chosen labels from columns?It is one more search condition to your << get rows where
dt << get rows where(:TestStage == Stage & :label == "USL" & !Is Missing(Column(dt, resCol)[]))
Re: How to get non-empty row values corresponding to chosen labels from columns?The JSL as written only returns the first matching row's value. While
dt << get rows where( :TestStage == Stage & :label == "LSL" )
returns a matrix of all of the matching rows found, When the subscript of [1] is added
(dt << get rows where( :TestStage == Stage & :label == "LSL" ))[1]
it returns the row number of just the first row found, which in your example, returns a missing value.
@jthi Jarmo, suggests adding a further clarification to eliminate missing values, which would change your Get Rows Where() to
(dt << get rows where( :TestStage == Stage & :label == "LSL" & !IsMissing(Column(dt, resCol) ) )[1]
This is a fine solution, if you are just looking for the first non missing value. However, if your data may contain multiple rows of non missing LSL values, then you may not want the first non missing value found. You may want to do something like find the Max USL value, or the Mean USL value from all of the non missing rows returned. You could do something like
theUSL = dt << get rows where( :TestStage == Stage & :label == "LSL" & !Is Missing( Column( dt, resCol ) ) );
USLMat = [];
For Each( {foundRow, index}, theUSL, USLMat = USLMat || Column( dt, resCol )[index] );
theUSL = Mean( USLMat );
Re: How to get non-empty row values corresponding to chosen labels from columns?I'm trying to do the same thing and in my case
dt << Get Rows Where(:A == "AAA")
gives a list with one element.
So I have to use
:B[(dt << Get Rows Where(:A == "AAA"))[1]]
Re: How to get non-empty row values corresponding to chosen labels from columns?@miguello, is there a question?
Re: How to get non-empty row values corresponding to chosen labels from columns?I guess not. Just would like to understand which behavior is correct and why me and @Neo have it one way, and you have it another?
What does it depend on whether it gives matrix or single element list with matrix?
Re: How to get non-empty row values corresponding to chosen labels from columns?If you are returning only 1 row from the Get Rows Where() call, then there is no meaningful difference.
