topic Re: How to get non-empty row values corresponding to chosen labels from columns? in Discussions

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

Neo — Mon, 20 Nov 2023 16:20:49 GMT

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.

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

jthi — Mon, 20 Nov 2023 16:30:07 GMT

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?

Neo — Mon, 20 Nov 2023 16:42:33 GMT

@jthi Thanks.

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

jthi — Mon, 20 Nov 2023 16:42:58 GMT

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?

txnelson — Mon, 20 Nov 2023 17:39:50 GMT

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?

miguello — Thu, 25 Jan 2024 18:09:59 GMT

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?

txnelson — Thu, 25 Jan 2024 18:21:15 GMT

@miguello, is there a question?

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

miguello — Thu, 25 Jan 2024 20:53:13 GMT

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?

txnelson — Fri, 26 Jan 2024 04:22:48 GMT

If you are returning only 1 row from the Get Rows Where() call, then there is no meaningful difference.