Is the a quick way to transform Pass and Fail validations to numbers 1 and 0 respectively
@Itumeleng_sage Why do you need to convert it to 1 and 0? Are you trying to get the total number of Pass/Fail validations?
This is one way you can do it, but there may be other ways, depending on what you are trying to achieve.
2. Select the column with the Pass/Fail result. Click Existing column if you want to override the Pass/Fail with the new 1/0 value, or click New column to create an additional column to contain the 1/0 value.
3. Create your own function like the following, where if column value is Pass, then assign 1, otherwise, assign 0.
The quickest way is with a replace function but as illustrated above there are many ways you can do this, here is another
@Clinton Jones thank you for the response, I actually used replace to do fail and used another replace on the new fail transform column for pass it works but a long route. The solutions that you and @Sueann See I will implement them
@Sueann See thank you for response. Am export the infortmation to other platforms and I think i would be best to transform in aperture
This is a really interesting question, and now that it's been answered I wonder if I could jump on this thread and extend the question with a requirement I have as part of some capability research I've been doing.
What I'd like to do is have some form of conditional pass/fail and rank or prioritise the results.
e.g. only fail something if it's of a particular sub type, and where it's not a definite "pass", I'd like to flag with an additional status such as "review". i.e. could be converted to a pass or fail.
Is this an obvious one to solve and a question that comes up? How would you go about this ?
Finally as part of reporting DQ results, can we apply some ranking in the charts or analysis so that those that have a material impact can be prioritised. Wondered if this was the weighting element of the rules and if you had some good examples? Thanks
@DTAconsulting If I understand correctly this might be an exemplar of data that tells the kind of story you have in mind
A record may pass/fail but some sub-element determines the degree of fail
Columns 1,2,3,4 are evaluated but in aggregate fail because they cannot be verified against say, a reference address however even failing records pass some criteria because you evaluate each column independently also.
so rows 2 through 17 are all fails, but you believe that some data elements are more valuable than others, you may deem Locale and Region as superfluous and so ascribe these no value, but address City Postcode and Country are very valuable. Let's call these my important columns.
You might do null checks on all columns and only consider these last four as higher value. So the question then is how could you score this in Data Studio?
Taking this then, my workflow looks thus
In the transform I calculate a 'scoring' column, this is a simple numeric value based on whether i found a value or not, but you could use any concept of a score, my example is simple. If the 'important' column is missing data then -1 as the score, if it has data then give it a 1
When I run this through validation, i have a couple of choices on using weights. Since they are assigned at the rule group level my rules are either all part of one group or in their own individual groups. You can see how this plays out here:
this also surfaces at the row level
on top of this you could add grouping bands if you wanted to but i think sorting on row quality where row quality is below 100 possibly gets you where you want to be?
I would be interested in your thoughts and those of other community members.