Convert Pass and Fail to 0 and 1

Itumeleng_sage

Hi,

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

Regards,

Sueann See

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

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

Itumeleng_sage

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

@Sueann See thank you for response. Am export the infortmation to other platforms and I think i would be best to transform in aperture

Clinton Jones

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