A SemQL enricher operates only within the context of the current record and its related entities. It cannot, by itself, compare two attributes from the current record to an arbitrary table of reference data unless you have a direct relationship (such as a reference or parent-child link) modeled in Semarchy. That’s why your SemQL expression always returns “E”—it does not actually perform a lookup against the external table.
A Lookup Enricher is ideal for enriching an attribute using values from another table based on key attributes.
CASE WHEN [LookupEntity.PrimaryKey] IS NOT NULL THEN 'S' ELSE 'E' END
Here, [LookupEntity.PrimaryKey] refers to the primary key (or any non-nullable field) from the reference table/entity. If the lookup fails, this will be NULL.
Régis L'HOSTIS
Hello,
I come to you because we cannot obtain the result we want to enrich an attribute in semarchy.
Our wish is to be able to define a code “S” or “E” in an attribute “Code Etab”.
To do this, we must define it by comparing 2 attributes (Ex: A and B) that we import into semarchy that we want to compare with a table that contains the data of the attributes to compare.
In other words, the result we expect must be the following:
The data from the imported source “A” = The data from the table with the attributes in semarchy “A”
AND
The data from the imported source “B” = The data from the table with the attributes in semarchy “B”
THEN (If the values of A and B are similar)
In this case the “Code Etab” will be equal to “S”.
ELSE
The “Code Etab” will be equal to “E”.
Method 1:
We first tried to obtain this result with a SemQL enricher with the following code:
CASE
WHEN A_SRC_ID = VLT_SIEGES_SOCIAUX.A_SS_SOURCE_ID
AND B_PUB_ID = VLT_SIEGES_SOCIAUX.B_SS_PUBID
THEN 'S'
ELSE 'E'
END
The function works, but systematically returns the value “E”. The correspondence between the imported data and the table with the data does not seem to be done. But we have doubts about whether it works this way…
Method 2:
We understood that we could obtain the result we want by using an enrichment of the “Semarchy Lookup enricher” type.
Does this seem coherent to you?
If so, could you give us some configuration ideas based on our needs. Despite our research we have not found how to configure this type of enrichment in the documentation (in Custom SQL?) - Do you have any concrete examples of configuring this type of enricher.
Thanks in advance for your help.