Case statement in computed content

Hi community,

We have a usecase to set a property's value automatically: based on several conditions that property gets a value. First we considered creating a desktop plan for this. Then we figured it would be preferrable to have this value set automatically, something that can possibly be accomplished by applying computed content.

We have used computed content before, mainly for concatenating values (like described in Computed Content: Show attribute values based on existing attributes (ataccama.com)). But in this case we would need to define a case statement to define the conditions and determine the value.

So the question is, does someone know if sql-like case stamenent can be implemented as computed content? And if so, can an example be provided?

Thanks for your attention and kind regards,

Albert

Page 1 / 1

Hello @Albert de Ruiter,

Yes, sql-like case stamenent can be implemented as computed content. See below a simple example of a computed content property for Catalog Item entity, serving a purpose of defining a status whether it is reviewed by Data Steward or not based on the description property.

Catalog Item when description is null, custom property shows “PENDING”.

Catalog Item when description is not null, custom property shows “READY”.

Configuration of Catalog Item entity in Metadata Model

Configuration of the computed content entity:

Here is the SQL query:

select
        a.id_i       ,
        a.parent_id_i,
        a.from_h     ,
        a.path_i     ,
        a.type_i     ,
        case
            when catalogitem_description is null then 'PENDING'
            else 'READY' 
        end as status,
        concat( 'https://mdm-server-one-fyg1u.worker-01-euc1.prod.ataccama.link/runWorkflow?id=WF1:export_all_instance.ewf&inputId=', a.id_i) as url
from
        (
                select
                        ci.$ci_id$           as id_i       ,
                        ci.$ci_id$           as parent_id_i,
                        ci.$ci_from$         as from_h     ,
                        $path(ci.$ci_path$)$ as path_i     ,
                        $type()$             as type_i     ,
						ci.$ci_name$         as catalogitem_name,
						ci.$ci_def$          as catalogitem_description,
                        ci.$ci_id$           as catalogitem_gid
                from
                        $ci$ ci
        ) as a

Hope this helps! If you have any questions, let me know in the comments below!

Hi @abir ,

Thanks for your example. I'll give it a try to make it fit our usecase. Likely I will have a follow-up question about joining tables, which works different as compared to regular SQL, because in our usecase the case statement will use logic involving several entities. But I will give it a shot myself first.

Kind regards,

Albert

Hi @Albert de Ruiter

You’re welcome! And joining multiple entities is also doable. Here is another example where entities are joined together.

select id_i, parent_id_i, from_h, path_i, type_i, 
concat(
'https://ataccama.atlassian.net/jira/secure/CreateIssueDetails!init.jspa?pid=10000&issuetype=10005&labels=AccessRequest&summary=Access%20Request%20for%20',
catalogitem_name,
'%20from%20',
connection_name,
'&customfield_10033=',
schema_name,
'&customfield_10034=',
catalogitem_name,
'&customfield_10035=',
owner,
'&customfield_10037=https%3A%2F%2Fone.local.ataccama.pro%2Fcatalog%2Fdata%2FcatalogItem%2F',
catalogitem_gid,
'%2F&customfield_10038=',
connection_name,
'&description=Hi%2C%0A%0ACould%20I%20please%20get%20access%20to%20',
catalogitem_name,
'%20from%20',
connection_name,
'%3F')
 as url from 
(select 
    ci.$ci_id$ as id_i,
    ci.$ci_id$ as parent_id_i,
    ci.$ci_from$ as from_h,
    $path(ci.$ci_path$)$ as path_i,
    $type()$ as type_i,
    l.$l_name$ as schema_name,
    c.$c_name$ as connection_name,
    p.$p_username$ as owner,
    ci.$ci_name$ as catalogitem_name,
    ci.$ci_id$ as catalogitem_gid
from 
    $ci$ ci 
    left join $l$ l on l.$l_id$ = ci.$ci_pid$ 
    left join $c$ c on c.$c_id$ = ci.connection_ri
    left join $p$ p on p.$p_id$ = ci.owner_ri) as a

Hi @abir ,

I wanted to apply your input, but I ran into the following. One of the entities that I need in the computed content is an extension of term. When trying to add this it cannot be selected (not visible in the dropdownbox). I can see term itself, but none of the extensions of term that I created. Is that expected behaviour (version 13.9)? Otherwise I'll contact support.

Kind regards,

Albert

I have contacted Support about the last question. I will update this post when I have more clarity.

HI @abir I am trying to implement similar solution. Below are my configurations: When I create referenced object in catalog item and reference computedContent, it gives error that it cannot be referenced. Do I create arbitrary referend object?

Hi @sumisha ,

Indeed you cannot reference to the computed content directly. It should work by following these two steps:

In the create an ‘embedded object’ property, where the object is your computed content entity.
Create a ‘delegated scalar property’, with ‘Via property’ being the name of the computed content entity (in your case ‘computedContent’) and the ‘Delegate property’ being the name of the property in ‘computedContent’ that you want to apply (so in your case ‘layer').

If you are interested you can refer to an article that I wrote about this topic: Business glossary – computed content (part 4).

Kind regards,

Albert

Thanks Albert for your reply.

Hi Community,

I forgot to update you regarding the follow-up from Ataccama Support, that investigated why the computed content didn't work for my example.

In short, the issue is that one of the entities that I need in the computed content is an extension of term. When trying to add this entity in the computed content screen it cannot be selected (it is not visible in the dropdownbox).

The feedback I received was that it is considered a bug, being fixed in version 15.4.

Kind regards,

Albert

Thank you for the update @Albert de Ruiter 😊

Hello, I need further support. I need to join attribute and termInstance but getting error when I bring in attribute of display name from termInstance.