Case statements in Aperture Data Studio V2

JenJ

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.

Josh Boxer

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

Hi Josh,

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

Regards,

Jencil.

JenJ

Hi Josh,

I am getting below error:

Parameter definition 'first_name' is not allowed.

Parameter definition 'name_middle' is not allowed.

Parameter definition 'last_name' is not allowed.

Parameter definition 'address_line_5' is not allowed.

Parameter definition 'address_line_6' is not allowed.

Unknown parameter: first_name

Unknown parameter: last_name

Unknown parameter: name_middle

Also, see attached screenshot for reference. Also, is there a documentation available for the scripting language? i.e. syntax and examples. If so, please can you share the resources?

Screenshot 2025-01-28 181609.png

Josh Boxer

I created the parameters as placeholders since I do not have a dataset with those columns. You can do the same create the parameters before opening the script editor:

https://docs.experianaperture.io/data-quality/hosted-aperture-data-studio/data-studio-objects/functions/#testingyourfunctionwithparameters~custom-functions

but you will want to replace the parameters with your columns at some point.

It is not really suggested to write functions solely in script editor, but used in conjunction with the canvas as an accelerator for certain situations. If you open the panel above the script box there are some tips like using Ctrl+Space to see a list of relevant functions.

JenJ

Hi Josh,

Thank you for clarifying. Make sense.

Best Regards,

Jencil.