Trying to get only instances where the Yes/No Condition exists

[Nicolas_St-Laur]

Hi there,

I am trying a new approach to an issue I had previously.

Here is the issue: I am trying to get only the instances where ERG shows up in the TestID ERG List.

Here is my expression that I am using:

Creating a Virtual Column:
Select(TESTID List[Test ID], IN([RG_NAME], (SPLIT(TESTID LIST[ERG LIST],“,”))))

With this I am close, but I am getting the whole list of TESTID List[Test ID], I just want the intances where RG_Name appears in the ERG List and grab the Test ID when it appears.

Example:
Table: TestID List

Table: ERG_TechRole

Using the function above brings back all TestID’s, not just TS_Security

Any help is appreciated.

[Steve]

Nicolas_St-Laurent:

Here is the issue: I am trying to get only the instances rows where ERG the value of the RG_Name column shows up in the TestID ERG List the ERG List column value (an EnumList) of the TestID List table.

Nicolas_St-Laurent:

With this I am close, but I am getting the whole list of TESTID List[Test ID], I just want results from the intances rows where the RG_Name column value appears in the ERG List column and grab the row’s Test ID column value when it appears.

Nicolas_St-Laurent:

Using the function expression above brings back all TestID’s, not just TS_Security

Try:

SELECT(
  TestID List[Test ID],
  IN([_THISROW].[RG_Name], [ERG List])
)

[Nicolas_St-Laur]

Thank you so much Steve. That worked perfectly.

Sorry my wording isn’t the greatest, and thank you for fixing it.

I was wondering, could you tell me the difference between my equation and yours?

[Steve]

Your expression:

SELECT(
  TestID List[Test ID],
  IN(
    [RG_Name],
    SPLIT(
      TestID List[ERG List],
      ","
    )
  )
)

My expression:

SELECT(
  TestID List[Test ID],
  IN(
    [_THISROW].[RG_Name],
    [ERG List]
  )
)

You were very close, actually. Mine prefixes [RG_Name] with [_THISROW]., but it isn’t strictly needed in this case. The big difference is is the the list of values we’re comparing to.

In my case, the expression is using [ERG List], which refers to the ERG List column value of each row in the TestID List table. In your expression, you use SPLIT(TestID List[ERG List], ","), which first uses TestID List[ERG List] to create a list of all ERG List column values in the TestID List table.

Since each ERG List column value is itself a list, and you’re creating a list from those, you’re creating a list of lists. SPLIT() “flattens” that list-of-lists into a single list of all the values in all of the component lists. Then you look to see if the RG Name value occurs in that list.

The problem here is that flattened list-of-lists includes the ERG List values from all rows in the TestID List table, which isn’t really what you want to check. You only want the current row’s ERG List values.

In effect, your expression answers the question, “does this RG Name occur in any ERG List?”, where mine answers the question, “does this RG Name occur in this row’s ERG List?”

See also: