Use case: An Alteryx subscriber needs to build a process to validate VIN numbers to test for data quality issues across the fleet data. The algorithm is provided at the below wiki link.
Objective: Please take the VIN numbers from the Input and create a new column for the check digit. Compare the check digit with the calculated check digit value. If the two values do not match (and there was no error in the calculation), then there is a mistake in the VIN. Out of the six VINs, how many vehicles have invalid codes?
I took on the challenge of doing this without lookup tables.
IF [Position]=8 THEN 10 ELSE 9-MOD([Position],10) ENDIF * IF IsInteger([Letter]) THEN ToNumber(Letter) ELSE CharToInt([Letter])- IF CharToInt([Letter])<=73 THEN 64 ELSEIF CharToInt([Letter])<=82 THEN 73 ELSE 81 ENDIF ENDIF
IF !REGEX_Match([VIN ID], '^[^IOQ]+$')
OR !REGEX_Match([VIN ID], '^[A-Z_0-9]+$')
ELSEIF Mod([Value],11)=10 THEN 'X'
ELSE ToString(Mod([Value],11)) ENDIF
This was a great skill-builder exercise for me. Now, I appreciate the value of parsing a string into (readers' guess: Columns or Rows), in order to allow for all of that SQL-esque stuff. Good stuff!
Great challenge - loved the fact that it required research.
My solution was kind of a hybrid solution (when I compared it afterwards to some of the other solutions)
My solution. I remember solving this one last year trying to prep for the Grand Prix qualification rounds! But I didn't save my solution... and I didn't know how to use several of the tools I used this time... so no idea how I did it a year ago!