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
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])
),
)
)
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])
)
)
)
Sorry Steve, I canโt see the LOOKUP function
Wrap either of the expressions I provided with ANY() to get the same result as LOOKUP().
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 ()
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.
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?
Not at all! Just rename it here:
You will, however, have to change its name wherever it appears in expressions.
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
Try this instead:
LOOKUP(
CONCATENATE(
LOOKUP(
[_THISROW].[Risk ID],
"Consolidated Risks",
"Risk ID",
"Risk Rating"
),
[Control Score]
),
โRisk Scaleโ,
โRisk Controlโ,
โResidual Riskโ
)
See also:
hereโs the result of the given suggestion,
hi steve, tried your suggestion but still get the same result, only HIGH value is displayed
Alrighty! How about this?
LOOKUP(
CONCATENATE(
LOOKUP(
[_THISROW].[Risk ID],
"Consolidated Risks",
"Risk ID",
"Risk Rating"
),
[_THISROW].[Control Score]
),
โRisk Scaleโ,
โRisk Controlโ,
โResidual Riskโ
)
Thanks for the suggestions and help, Steve. Made some changes with the table and was able to get the correct results. Used the same formula (expression) though.
User | Count |
---|---|
41 | |
36 | |
30 | |
23 | |
16 |