Skip to main content
Solved

Advanced Expression in IDE (Atacaama Desktop) to perform a uniqueness chaeck

R
Data Voyager
Forum|alt.badge.img+1

I have been trying for some time to build a data quality check/rule in Ataccama Desktop (using an advanced expression) to check where a defiend attribute is unique/distinct and have been struggling to find the correct function to use for this.

In a tool like excel I woudl simply count the number of instances of each value and following that I would ensure that anything where the count was >1 failed this check but I have thus far been unable to find a way to perform a distinct count in this way. e.g. DistinctCount([Install_And_Leave_Flag]) = 1

I understand that the profiling step nativley gives you this information but I require a way to do that via an advanced expression for my use case. 

 

Best answer by DannyRyan

Hi @rpainte1,

Record Descriptors are one possible way to achieve your end goal.

There is another post on how to use Record Descriptors, which you might find useful. I have added the link to the post below.


For your use case, I have attached a ONE Desktop plan to demonstrate how it can be used to perform record by record analysis to determine if the value on the current record is distinct.

 

Plan using Randomly generated records

The plan randomly generates some records, either null, install or leave.

Re-run the plan a few times, if you do not get a distinct value the first time. 😀

 

After you have run the plan, have a look at the output.
 

Output showing null and Install values are distinct in the recordset.

Eyeballing, we can see that the null and Install values are distinct.

The Record Descriptor Group Size is equal to 1, and we have explained this observation in the expLAIN and scoRE columns.

I hope this plan helps you with your business rules.
Please let us know how you get on.

Have a great day.

Danny

View original
1 Attachments
Catherine
Cansu
2 people like this
  • Catherine
    Catherine
  • Cansu
    Cansu
Did this topic help you find an answer to your question?

6 replies

DannyRyan
Ataccamer
Forum|alt.badge.img+2
  • DannyRyanAtaccamer
  • Head of Technical Training
  • 41 replies
  • Answer
  • April 17, 2023

Hi @rpainte1,

Record Descriptors are one possible way to achieve your end goal.

There is another post on how to use Record Descriptors, which you might find useful. I have added the link to the post below.


For your use case, I have attached a ONE Desktop plan to demonstrate how it can be used to perform record by record analysis to determine if the value on the current record is distinct.

 

Plan using Randomly generated records

The plan randomly generates some records, either null, install or leave.

Re-run the plan a few times, if you do not get a distinct value the first time. 😀

 

After you have run the plan, have a look at the output.
 

Output showing null and Install values are distinct in the recordset.

Eyeballing, we can see that the null and Install values are distinct.

The Record Descriptor Group Size is equal to 1, and we have explained this observation in the expLAIN and scoRE columns.

I hope this plan helps you with your business rules.
Please let us know how you get on.

Have a great day.

Danny

1 Attachments
Danny | Did it answer your question? Mark it as the correct answer!
R
Data Voyager
Forum|alt.badge.img+1
  • rpainte1Data Voyager
  • Author
  • Data Voyager
  • 2 replies
  • April 19, 2023

Thanks Danny for the detailed help! 

It was exactly what I was after and I have now managed to slightly alter and repurpose the plan you shared to work for my use case!

Cheers,

Ross

DannyRyan
1 person likes this
  • DannyRyan
    DannyRyan
M
Ataccamer
Forum|alt.badge.img+2
rpainte1 wrote:

I have been trying for some time to build a data quality check/rule in Ataccama Desktop (using an advanced expression) to check where a defiend attribute is unique/distinct and have been struggling to find the correct function to use for this.

In a tool like excel I woudl simply count the number of instances of each value and following that I would ensure that anything where the count was >1 failed this check but I have thus far been unable to find a way to perform a distinct count in this way. e.g. DistinctCount([Install_And_Leave_Flag]) = 1

I understand that the profiling step nativley gives you this information but I require a way to do that via an advanced expression for my use case. 

 

Good afternoon--

Just a thought, as you’re licenced to use the ONE Web application, this is functionality that is possible to acheive there. Do you feel that you could switch to the Web interface instead for this?

If you’d like a quick call to run through the options, maybe drop me a DM/email and I could set something up?

 

Matthew Taylor

(Ataccama UK)

 

DannyRyan
1 person likes this
  • DannyRyan
    DannyRyan
R
Data Voyager
Forum|alt.badge.img+1
  • rpainte1Data Voyager
  • Author
  • Data Voyager
  • 2 replies
  • April 20, 2023

Yes, aware we can perform this check (much more easily in fact!) via the web application and we are doing this for a number of datasets already. We were previously using IDE however and while we are migrating what plans we can over to web, with the strategic goal of getting it all moved over, this is taking  time and there are some genuine internal reasons why, for a small number of datasets, we are still reliant on IDE for the time being!

 

Thanks for the offer though!

 

Cheers, 

Ross

DannyRyan
1 person likes this
  • DannyRyan
    DannyRyan
S
Data Voyager
  • suhasjhaData Voyager
  • Data Voyager
  • 1 reply
  • May 16, 2024

@matthewtayloruk  can you share the steps for doing this in ONE web?

Cansu
Community Manager
Forum|alt.badge.img+3
  • CansuCommunity Manager
  • Community Manager
  • 625 replies
  • May 21, 2024

Hi @suhasjha, thank you for posting! May I ask if you’ve had a chance to check our documentation on this? 

Check out our Quick Start Guide to get started on the community 🙋‍♀️

Reply


Most Liked this week

Community Terms of Use & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings