We've recently made an accessibility improvement to the community and therefore posts without any content are no longer allowed. Please use the spoiler feature or add a short message in the message body in order to submit your weekly challenge.

Weekly Challenge

Solve the challenge, share your solution and summit the ranks of our Community!

Also available in | Français | Português | Español | 日本語
IDEAS WANTED

We're actively looking for ideas on how to improve Weekly Challenges and would love to hear what you think!

Submit Feedback

Challenge #47: Vehicle Identification Number Test

GeneR
Alteryx Alumni (Retired)

Happy Halloween loyal weekly exercise participants, here is an exercise I hope does not scare you too much. Hopefully is a treat and not a trick.  The link to last week’s challenge  is HERE.

 

Halloween.jpg

 

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.

 

http://en.wikipedia.org/wiki/Vehicle_identification_number

 

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?

Joe_Mako
12 - Quasar

I took on the challenge of doing this without lookup tables.

 

Spoiler
Here is my calculation to get the weighted value:
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

and then to convert the sum to the check digit:
IF !REGEX_Match([VIN ID], '^[^IOQ]+$')
OR !REGEX_Match([VIN ID], '^[A-Z_0-9]+$')
THEN Null()
ELSEIF Mod([Value],11)=10 THEN 'X'
ELSE ToString(Mod([Value],11)) ENDIF

I think the VIN of "ABCDEFGHIJKLMNOPQ" should come back with a Null because it is invalid as it contains an I,O,Q. I also verify that it is only capital letters and numbers.

VIN.png
TaraM
Alteryx
Alteryx

A solution has been posted

Spoiler
2016-11-07 09_08_13-Alteryx Designer x64 BETA - Analysis_ValidateVIN_Solution.yxmd.png
Tara McCoy
DanielUpton
9 - Comet
Spoiler
near-spoiler alert

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!

MarqueeCrew
19 - Altair
19 - Altair

working on earning a badge ... 

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
SeanAdams
17 - Castor
17 - Castor

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)

 

Spoiler
I used the simple regex to tokenise into rows (like @MarqueeCrew and @Joe_Mako did too) but split it into streams like @TaraM

For anyone who needs the lookup tables they are in the attached flow.

 

NicoleJohnson
14 - Magnetar
14 - Magnetar

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!

Spoiler
WeeklyChallenge47.JPG
EstherB47
15 - Aurora
15 - Aurora

Added the fun of parsing the lookup tables from the Wiki page (to avoid any typos)

LordNeilLord
15 - Aurora

Not the neatest solution but it works, so I'm satisfied

 

Spoiler
Weekly Challenge 47.png
jamielaird
14 - Magnetar

Here's my solution.  I've mostly used formula tools because they suit the way my brain works.  Enjoyed this one lots!

 

Spoiler
Untitled.png