Convert Pass and Fail to 0 and 1


Is the a quick way to transform Pass and Fail validations to numbers 1 and 0 respectively


Best Answers

  • Sueann SeeSueann See Administrator
    Accepted Answer

    @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.

    1. Assuming you are using Show all rows, connect a Transform step.

    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.

  • Clinton JonesClinton Jones Experian Elite
    Accepted Answer

    The quickest way is with a replace function but as illustrated above there are many ways you can do this, here is another

  • Itumeleng_sageItumeleng_sage Super Learner
    Accepted Answer

    @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


  • Itumeleng_sageItumeleng_sage Super Learner

    @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

  • Clinton JonesClinton Jones Experian Elite
    edited April 22

    @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.

Sign In or Register to comment.