LOOKUP if one of the columns filled in by the user...

d_alvarez · 07-30-2020 11:41 AM

Good afternoon, first I thank you in advance for your help.

Please could you give me an expression to be able to obtain the value of a column in a table if any data from 4 columns of another table matches.

The user must complete this information in a form and if any data from this list matches

Identification document,
Email,
Telephone,
Mobile phone

The expression must search in another table under the name Pre-Approval Data and bring the result from the Agent Code column.

please could you help me with this expression since I have tried but I cannot understand very well this expression of LOOKUP for the search of various data

Steve

Try:

SELECT(
  Pre-Approval[Agent Code],
  OR(
    AND(
      ISNOTBLANK([_THISROW].[Identification document]),
      ([_THISROW].[Identification document] = [Identification document])
    ),
    AND(
      ISNOTBLANK([_THISROW].[Email]),
      ([_THISROW].[Email] = [Email])
    ),
    AND(
      ISNOTBLANK([_THISROW].[Telephone]),
      ([_THISROW].[Telephone] = [Telephone])
    ),
    AND(
      ISNOTBLANK([_THISROW].[Mobile phone]),
      ([_THISROW].[Mobile phone] = [Mobile phone])
    ),
  )
)

Steve

More efficient:

UNIQUE(
  IFS(
    ISNOTBLANK([Identification document]),
      SELECT(
        Pre-Approval[Agent Code],
        ([_THISROW].[Identification document] = [Identification document])
      )
  )
  + IFS(
    ISNOTBLANK([Email]),
      SELECT(
        Pre-Approval[Agent Code],
        ([_THISROW].[Email] = [Email])
      )
  )
  + IFS(
    ISNOTBLANK([Telephone]),
      SELECT(
        Pre-Approval[Agent Code],
        ([_THISROW].[Telephone] = [Telephone])
      )
  )
  + IFS(
    ISNOTBLANK([Mobile phone]),
      SELECT(
        Pre-Approval[Agent Code],
        ([_THISROW].[Mobile phone] = [Mobile phone])
      )
  )
)

d_alvarez

Sorry Steve, I can’t see the LOOKUP function

Steve

Wrap either of the expressions I provided with ANY() to get the same result as LOOKUP().

d_alvarez

Sorry Steve I think I have not explained myself well, when a user enters the data in the form in the columns

DNI | NIE | CIF
Correo Electrónico
**Teléfono **
Teléfono Móvil

I must look in the table “Pre-Approval Data” these data of the columns entered by the user and if one of them matches I must bring the value of the column "Agent Code"

This is why I mentioned that I understand that this should work with the expression LOOKUP ()

Steve

First, this:

means that this table name:

cannot have a - character in its name. You will need to rename the table to remove the - character.

Second, wrap the entire expression with ANY():

ANY(
UNIQUE(
...
)
)

The result should be what you want.

LOOKUP() has limitations and is intended for situations simpler than yours; it is not a good choice for your needs as I understand them.

d_alvarez

Sorry Steve, in this table I have many expressions if I change the name of the table this means that I have to delete the current table and reload it because it would lose all the detailed expressions in the columns, and this table has more than 100 columns.

Did you open any way to change this without losing expressions?

Steve

Not at all! Just rename it here:

You will, however, have to change its name wherever it appears in expressions.

Steve

Marian_VELOSO

hi, need help with my expression using LOOKUP(). been getting only 1 result value from the lookup table (Risk Scale), and that result is the text value “High”. other values are blank or no result.

LOOKUP(CONCATENATE(SELECT(Consolidated Risks[Risk Rating], ([Risk ID]=[_THISROW].[Risk ID])), [Control Score]),
“Risk Scale”,
“Risk Control”,
“Residual Risk”)

Risk Scale table

Steve

Try this instead:

LOOKUP(
  CONCATENATE(
    LOOKUP(
      [_THISROW].[Risk ID],
      "Consolidated Risks",
      "Risk ID",
      "Risk Rating"
    ),
    [Control Score]
  ),
  “Risk Scale”,
  “Risk Control”,
  “Residual Risk”
)

LOOKUP if one of the columns filled in by the user matches