Case statements in Aperture Data Studio V2

JenJ
JenJ Member
in General

Hi everyone,

I’m looking to convert a SQL query in Aperture, which contains multiple CASE statements for a single column in the dataset. I’m looking for the best practices to achieve this efficiently and clearly. The column I’m working with contains various conditions that need to be evaluated.

Could anyone share their insights or examples on how to structure multiple CASE statements effectively?

Thanks in advance for your help!

Please find below snippet of the code:

CASE WHEN TRIM(LOWER(first_name)) like 'mtt%' OR TRIM(LOWER(first_name)) like '%ltd%' OR TRIM(LOWER(first_name)) like '%homes%' OR TRIM(LOWER(first_name)) like '%limited%' OR TRIM(LOWER(first_name)) like '%builders%' OR TRIM(LOWER(first_name)) like '%building%' OR TRIM(LOWER(first_name)) like '%house%' OR TRIM(LOWER(first_name)) like '%trust%' OR TRIM(LOWER(first_name)) like '%property%' OR TRIM(LOWER(first_name)) like '%group%' OR TRIM(LOWER(first_name)) like '%council%' OR TRIM(LOWER(first_name)) like '%association%' OR TRIM(LOWER(first_name)) like '%partnership%' OR TRIM(LOWER(first_name)) like '%home%' OR TRIM(LOWER(first_name)) like '%living%' OR TRIM(LOWER(first_name)) like '%housing%' OR TRIM(LOWER(first_name)) like '%properties%' OR TRIM(LOWER(first_name)) like '%services%' OR TRIM(LOWER(name_middle)) like '%ltd%' OR TRIM(LOWER(name_middle)) like '%homes%' OR TRIM(LOWER(name_middle)) like '%limited%' OR TRIM(LOWER(name_middle)) like '%builders%' OR TRIM(LOWER(name_middle)) like '%building%' OR TRIM(LOWER(name_middle)) like '%house%' OR TRIM(LOWER(name_middle)) like '%trust%' OR TRIM(LOWER(name_middle)) like '%property%' OR TRIM(LOWER(name_middle)) like '%group%' OR TRIM(LOWER(name_middle)) like '%council%' OR TRIM(LOWER(name_middle)) like '%association%' OR TRIM(LOWER(name_middle)) like '%partnership%' OR TRIM(LOWER(name_middle)) like '%home%' OR TRIM(LOWER(name_middle)) like '%living%' OR TRIM(LOWER(name_middle)) like '%housing%' OR TRIM(LOWER(name_middle)) like '%properties%' OR TRIM(LOWER(name_middle)) like '%services%' OR TRIM(LOWER(name_middle)) like 'mtt%' OR TRIM(LOWER(last_name)) like 'mtt%' OR TRIM(LOWER(last_name)) like '%ltd%' OR TRIM(LOWER(last_name)) like '%homes%' OR TRIM(LOWER(last_name)) like '%limited%' OR TRIM(LOWER(last_name)) like '%builders%' OR TRIM(LOWER(last_name)) like '%building%' OR TRIM(LOWER(last_name)) like '%house%' OR TRIM(LOWER(last_name)) like '%trust%' OR TRIM(LOWER(last_name)) like '%property%' OR TRIM(LOWER(last_name)) like '%group%' OR TRIM(LOWER(last_name)) like '%council%' OR TRIM(LOWER(last_name)) like '%association%' OR TRIM(LOWER(last_name)) like '%partnership%' OR TRIM(LOWER(last_name)) like '%home%' OR TRIM(LOWER(last_name)) like '%living%' OR TRIM(LOWER(last_name)) like '%housing%' OR TRIM(LOWER(last_name)) like '%properties%' OR TRIM(LOWER(last_name)) like '%services%' OR LOWER(address_line_1) like '%mtt%' OR LOWER(address_line_2) like '%mtt%' OR LOWER(address_line_3) like '%mtt%' OR LOWER(address_line_4) like '%mtt%' OR LOWER(address_line_5) like '%mtt%' OR LOWER(address_line_6) like '%mtt%' AND TRIM(UPPER(first_name)) NOT IN ('MTTUSKER','MTTU','MTTONDA','MTTREERY','MTTA','MTTAIRI','MTTULLOUTH','MTTYE','MTTHESON','MTTHEWS') AND TRIM(UPPER(last_name)) NOT IN ('MTTUSKER','MTTU','MTTONDA','MTTREERY','MTTA','MTTAIRI','MTTULLOUTH','MTTYE','MTTHESON', 'MTTHEWS') AND TRIM(UPPER(name_middle)) NOT IN ('MTTUSKER','MTTU','MTTONDA','MTTREERY','MTTA','MTTAIRI','MTTULLOUTH','MTTYE','MTTHESON', 'MTTHEWS') THEN 1 ELSE 0 END AS sample_flag,

Thanks.

Answers

  • Josh Boxer
    Josh Boxer Administrator
    edited January 24

    Hello, I cannot think of a way to convert this in one click, but once you use Transform to add a new column named 'sample_flag', it is possible to create using a combination of the Function canvas and script editor. Here is example using some of the comparisons in red:

    param first_name;
    
param name_middle;
    
param last_name;
    
param address_line_5;
    
param address_line_6;
    

var firstNameTrim = Trim(Trim([p:first_name], true, ' '),false,' ');
    
var lastNameTrim = Trim(Trim([p:last_name], true, ' '),false,' ');
    
var nameMiddleTrim = Trim(Trim([p:name_middle], true, ' '),false,' ');

    
IfThenElse(
    
And(
    
Or(
    
MultiCompare(LowerCase([v:firstNameTrim]), false, false, false, {('Starts with','mtt'), ('Contains','ltd')}),
    
MultiCompare(LowerCase([v:nameMiddleTrim]), false, false, false, {('Contains','ltd'), ('Contains','homes')}),
    
MultiCompare(LowerCase([v:lastNameTrim]), false, false, false, {('Starts with','mtt'), ('Contains','ltd')}),
    
MultiCompare(LowerCase([p:address_line_5]), false, false, false, {('Contains','mtt')}),
    
MultiCompare(LowerCase([p:address_line_6]), false, false, false, {('Contains','mtt')})
    
),
    
Equals(UpperCase([v:firstNameTrim]), true, false, false, false, false, false, false, {'MTTUSKER', 'MTTU'}),
    
Equals(UpperCase[v:lastNameTrim]), true, false, false, false, false, false, false, {'MTTUSKER', 'MTTU'}),
    
Equals(UpperCase[v:nameMiddleTrim]), true, false, false, false, false, false, false, {'MTTUSKER', 'MTTU'})
    
),
    
1,
    
0
    
)

    If you made this entire case (so the And Function going into the IfThenElse) into a Variable then the Preview table will show results just for this case and then a similar Variable for the next case you add to this. https://docs.experianaperture.io/data-quality/hosted-aperture-data-studio/data-studio-objects/functions/#function-script-editor

  • JenJ
    JenJ Member

    Hi Josh,

    Thanks for solution. I will try the above suggestion and update you.

    Regards,

    Jencil.

Categories