nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to get non-empty row values corresponding to chosen labels from columns?

Neo

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 (encircled blue).

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

There are other columns in my data table populated similarly for other test test stages/labels.

Accepted answers

jthi

You could add additional check using IsMissing()

... & !IsMissing(Column(dt, resCol)[])

jthi

It is one more search condition to your << get rows where

dt << get rows where(:TestStage == Stage & :label == "USL" & !Is Missing(Column(dt, resCol)[]))

All comments

jthi

You could add additional check using IsMissing()

... & !IsMissing(Column(dt, resCol)[])

Neo

@jthi Thanks.

jthi

It is one more search condition to your << get rows where

dt << get rows where(:TestStage == Stage & :label == "USL" & !Is Missing(Column(dt, resCol)[]))

txnelson

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

miguello

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]]

txnelson

@miguello, is there a question?

miguello

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?