Output
Input
Output
Input
Output
Input
Output
Input
Input
Output
iVBORw0KGgoAAAANSUhEUgAAAXoAAAA0CAIAAAAL52RvAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAAEnEAABJxAfPceo4AAAASdEVYdFNvZnR3YXJlAEdyZWVuc2hvdF5VCAUAAA0cSURBVHhe7Z3fjhXHEYf9Bs4bxG+A38B+A/sN7DdI3sBccYcSKXBhCQkuLPliibGEfcNeJGKvFiFhNgoKijFai+BVEPJCIrAlS+TbU72V2uo/02dn5uyedX1qjU7XVPd0D1O/qZ4zZ3nrTRAEwUoIuQmCYEWE3ARBsCJORm7+8uQ/Nx7tp0qwDjx58uTu3bu7u7upHgTLs2q5efzi5/e/+PatP31DSaZgHQi5CcazUrm59+zVbz79m2jNsnLz40+/nN/eQ6oQrGQKVkjITTCelcrNJ9s/qNYsJTdWp1iIJWuwQkJugvGsh9y8/emOtgq5ORFCboLxrIfc2FYhNydCyE0wnpCboIuQm2A8ITdBFyE3wXhCboIuQm6C8YTcBF2E3ATjGZCbH3/65Y/fPPt48/v3v/jWvjJDeffzh9jZe+/Zq+Q9xMnKDeM8v733+9v/Yi5s+fzloxdpXzBEyE0wngG5+eCr72yo18o71x4QwIMv4C0lN3+49290gYKu2VZoHEphy6AAXX3w3GmlFuz0gKom1xLs/euT/0pJpsXvMES5RLySdQG71L/ds6JNrHbzWftJpgp2hDO9BhlyE4xnQG4+2tx18dkohG5qVmEpubGe7fLen/+Z2mQQh06tigWfhi5YzUUE8URi1CIluS6wdk5gstZBDdX/t9f+nqxH3zb63e0nyZrh5thzxGMQchOMZ0BuuOty0RPPhBxiYQsWGw9S2okGraxzslawnu3SkJtzn/8jd5bi7ERsapNhnZl1nii5AVh5GpRgsIPkFCXrIr9TO6V2blEi67bW2c3+/j6HAD4kU5PXr1+LPyRTnU43RQbDIVJ9wbKd7O3tuU7EwjbVSxR9BluBjNmdPSzWKNUexH9Cxj4qdgoyodzkSlErNkQt7nBUbQrDZxeotX5ybXLFJRQ3Hu3bve3fvqMO1tmJhT0JrFjzFIwTrg6U2hTGM6vcEAk3b968fPnyBQPVzc1NF+0Cxq2trStXriTXQ7Ds7OwkpwzxuXXrVqo3efjwofi7KYuR0aZ6k9u3b4u/7eSzzz7DwjbVSxR9Fj1duH//fqqXkCOyTfUF0lCNUu1BTsLFixcbMscucb5z504y1RkrN+5yn1BulKUOobgwJlNIO45iFYd4Ttaj5HJDWofEMJJ7z16xzVWANZE6t1c3NoVxWRLQv+6luCUVx2XMuhdtSjtmYD65IX7kei3Ctc5Fn1wXcH1jTLsXEJbWgugUwyPtvnBhMEeAS5cuiXNRbmDwVKCJOqoJ5aYd/NPKDc6Dox10sJxZubE6Yh+IOIhYdaMUO3dyQ1TbB7pFXN6U65Fihenqg+fJanAnzY7QHWVwVGOYSW64JS4u7AOIBxtIHIskAvvGxkYyHdUa9lp/UiR6kL3FmJRWMBgbVgFrcjPYiUS+MKHcAHpaTPpADqrKIkgrZ1QYmzgU/3E5q7K3mFXpiWoooOXMyo19rlRLbQSrJowwWQ3WgW57otrlVkUdAZu80HNNlYpLKndaiiOfkDnkhmtULtbGHRu7DS1dQLmUR8FfFAfPZDoEo0pVYyKalUiC4zwXrVM/jXVNo5ORciPd1lZzk8sNSJ+sbZ3G6Rw716dwNuXm2KP64KvvktVg5SZf79SwGlHsFmx60lhzuenIksr2T4rUSKAmYQ65kaCCznuj3ktrkSNo/Dg5ECOxwZbgSdYMiS7cZHhuygddLAbAttGJ5GVs806KUuIo+mABPQlFsZOBufMj/rWTpqer8Y8roul6kGOhOE6GGpxNuXHf6SRrBTuqopocT27s19uUohzYFKw9L7du+vDrx7bac05GMrncaJZeu1HnSGrTc303wlXvycVwlb1yCOmkKDfaSTGGZWo48CHvpDg2R9EHC/BBtAxymRYJcKMS5+JQoUdu1IcZiUX/+WppZpGzKTdkCrZJslaYSW7QF21FyddT9gusxtMlgd7sUx5bJNmZm8nlRu/SndcrES7+PfKkj4RSfYFaGrdliWSJzFwp4KCLRScy/mN0UpQSR9EHC/CBI4ry5gscmZpTlkW7UXIDGxsb+Fy/fl2qPbPI6ZIbrvUvH704v73HTVXeo9XiXqI7JXJjBYLixuyK/XJnQrkBq3ocKFkPsXt7JMOdBykrWEYJk8uNBAZ0rqQ0KmphYymGkFjkc3F1IHdsdkkMS0S5KR90cbQT9+RCU5taJz2BWvTBAvJZH1Fp/AtyVt28Fu3Gyg3zkiPipk002elkQG4ev/j5483v3SXeKKdTbvrLtHLjXsCx79S43Me9blPDLakoK/sPLeaTm1QfQi/xWthY1NkOWCzyWXMTGzByA9dFVq4UcNDFYSd6FNuJtNKXUPJOilLiKPpggVSpPMmSs+pOUe5mKZ6rIpIzMioZXs8/hKMlN+T/tZ8a1coccuMegswqN8XntceWG7ArIPsFmZ3Uue73ZZzy9ryyPBXzyU3nHVKjoucqL4aQWFLlMKR1aSZNSFikCuLgpnzQRdaJphg9nYiFbaqXKPpggVRZII+9QfuXs+pOkfjUzpuMGdxMi+g3g5oDLkVVbrjfOq0hKtALCndUrnst1odqal/ieHLjWrUPIViBYNg61MGS2h9ljNzYwdvfSdg+88c6NZiLtpJC/2nfzMwnN519snwQ/x65kddhwWqZWFLFhJms5mq64Ia3aPH/TnRU4iZNND+CWrdsU71E0QcLpMoh7vH53HKjJ3apJ8RKVW7sD3/aL5uoG6UWscKJyM34FGCM3LgXcGTR5IydD1/cedAy69t9yuRyoxduj3wI8qwkf6EmR277NsuAxdGOhKusnohqWZi48JaYd1M+6OJoJ/JgGGeJ28FO9KCpXkJaOR8skCqHoKfySEVOy9xys5RzTlVu7AXdfk3Oep5CuaEk63EZIzdgm8uZtN/TN163sdgXAl1p/Lh0QiaXG5A4aby94pDAHrzWuc9Lz7pQEqRtqiwgVsUo/u6hda4UsHD3nehE2Dr/vBNRBJqkegkR1qJqpIpBhRuRXUu5IaTtBd2O8H7PlcmNazLyu5uRcmMf08jPsuyaqPNZr21CsuleLGK+yW825pAbiQ2oRYJD1YE7eePBgaqSXUmBGFPlEH384bQJOuUGdCIuH4G8E11/2TWXRUPayZ8YU+UoOgtZW6233CRrBeu5lNwcbxHRIzdu/P0PR4qMlBumaV/ns+oz+LqN4MRFUiQ7KsrcS6o55EbTEKj9nphAskKggU1cOTUBOrx+/bo45B2KPVUOkTFA3lu/3CzbiRjxz88nEoOdvblyHRy4IjcgfQrrLTftCO/3PIZwgAu2nnTAfc2cv/OyFCPlBtxrh1p6Xrdxz+x1ANitis29pJpDbkCjC1iMoBEcQuCzLE9cGGvyAojL1tYWqwm22LWrPFUB2ZUqBsQCUsXQLzewVCdMR4fKFHZ2dhYz3t3c3BQje11qA7IrVTI4uva5ZnLjnhQ0sgP31LOtIPbGTul8bOG0r7OVi/AxCc54uXFT0CJPjtvYo6Mvtkkx65mJmeQGiCtZAhQhXPPAYxkiTzdysNcWKeKQKh0sJTc1ip1AY9ZMIZ8yyN5UKaFasGZyA/bO+c61B3lgIEn5G4DcY+XvB5NQ5BGeP+/EU3omH5EXl/NFgVM0iv5dZLb9rXArLt8ahxbGyw3kP0E41/G6jROU/BmNHRtJkJyWOZhPbgQyFFISgpNbNPHGB5ZR7cPRhBDa2NjAmS2fsaR9JXCDVOmAAeDvIn+SThSUkZGL7jBrPte0EnoOzRnIO5GGtZ4ZmzjUBmlZyjmnKjf5C6woiPwHBh9+/di++F8rxW+gaz/80TL4m+xiKS4l3NpNCp4yEflBhp1IbT0yidzkgxlMuNAOu4wqypNT1ZHLxgZzy03wa6AqN9zzB6WBgk9DC/J8obas0FIUqTwtyktyPUrtoUmtFNOfSeQmz7aKx7K4E1tLvpyQzbSkCrkJxlOVG+D6zl9jtYUMiJghkOzKS4t70KBwVy/6S6n9aZgbj/YbrRoLkzxNa5TigCeRG7D9DD6Bcs+50JS0o4T9Z5ppSRVyE4ynJTcC1z0SoJkOMU/YcAu1N2c+Ez/qw9VPkDcuevzpgW7pSjqkEFG1G7hQbMWBBr+rYiQMr6FWjLwxYAYmbdkOroAaMHI94uCYmalMUOaYrBU4b+pMaZ/G4xFyE4xnWG7OEsQhcY58SCGkWdzNkQvkcBTVGmQrWdeHkJtgPL8uuTlB7JpuMFs5hYTcBOMJuVkFLAPtd0yryaemJeQmGE/IzSpg4aZa896Ih80nSMhNMJ6Qm9m59+yVTW3aL16fWkJugvGE3MwIa6jz23tWa9Y0tYGQm2A8ITcTg6AgK+8e/YvxUt6u/8d1p5+Qm2A8ITcT4yRGC1ozx+swKyPkJhhPyM3EOJWR8tHm7vrmNULITTCekJuJ0fee2X6y/cPVB8/X8WvvnJcvXz59+jT/81FB0E/ITRAEKyLkJgiCFRFyEwTBSnjz5n+Bsg0wC+hiuAAAAABJRU5ErkJggg==
Weekly Challenge #35
There is a series of data cleansing processes we need to do on our data. Please solve each per the instructions.
C:\Users\ALEKHS~1\AppData\Local\Temp\Engine_14524_d6ed65ebe46645a0993667d9859e36f9_\Engine_6348_44e6624670e94c5abf2cb10ae1021493_.yxdb
Single
Profile
C:\Users\ALEKHS~1\AppData\Local\Temp\Engine_14524_d6ed65ebe46645a0993667d9859e36f9_\Engine_6348_cb9c94ca2c8044d79498ffe9d1a2cc16_.yxdb
Single
Profile
003001
3001: Productivity HQ
001234
1234
C:\Users\ALEKHS~1\AppData\Local\Temp\Engine_14524_d6ed65ebe46645a0993667d9859e36f9_\Engine_6348_cfe8ad982ca248ed960793fcba19455c_.yxdb
Single
Profile
10002506SD
13413
151511324SD
SD2454542
134SD245
C:\Users\ALEKHS~1\AppData\Local\Temp\Engine_14524_d6ed65ebe46645a0993667d9859e36f9_\Engine_6348_b141368d85ac429294e19290a311e755_.yxdb
Single
Profile
FLMI03
USFLMI03
USFLMI03: MIRAMAR FL 2884 CORPORATE
C:\Users\ALEKHS~1\AppData\Local\Temp\Engine_14524_d6ed65ebe46645a0993667d9859e36f9_\Engine_6348_57c9550e5ca047faa70599c7805df497_.yxdb
Single
Profile
Compressed Air Systems and Svc
100
USCL1
150
CAIN2
50
Transport Solution NA EMEA
200
CACL2
250
Club Car
USIN1
Commercial HVAC NA and EMEA
USCL1
Compressed Air Systems and Svc
USIN2
Corporate
USCOR
Fluid Management
USIN3
HVAC and Transport LA
USCL2
HVAC Parts NA and Export
USCL3
Material Handling
USIN4
Parts and Supply Solutions
USCL3
Power Tools
USIN5
Residential HVAC
USCL5
Transport Solution NA EMEA
USCL6
C:\Users\ALEKHS~1\AppData\Local\Temp\Engine_14524_d6ed65ebe46645a0993667d9859e36f9_\Engine_6348_261d49f6595e48d1b19085de25f08cbd_.yxdb
Single
Profile
003001
3001
3001: Productivity HQ
3001
001234
1234
1234
1234
C:\Users\ALEKHS~1\AppData\Local\Temp\Engine_14524_d6ed65ebe46645a0993667d9859e36f9_\Engine_6348_b6fcfc9b822240f19171c5f47fd69b0d_.yxdb
Single
Profile
10002506SD
10002506
13413
13413
151511324SD
151511324
SD2454542
SD2454542
134SD245
134SD245
C:\Users\ALEKHS~1\AppData\Local\Temp\Engine_14524_d6ed65ebe46645a0993667d9859e36f9_\Engine_6348_fc43ffc752b441c3b727fe4f8290bcce_.yxdb
Single
Profile
FLMI03
USFLMI03
USFLMI03
USFLMI03
USFLMI03: MIRAMAR FL 2884 CORPORATE
USFLMI03
C:\Users\ALEKHS~1\AppData\Local\Temp\Engine_14524_d6ed65ebe46645a0993667d9859e36f9_\Engine_6348_83936e7bb74c4426adf153c2cf728446_.yxdb
Single
Profile
USIN2
100
USCL6
200
CACL2
250
CAIN2
50
USCL1
150
C:\Users\ALEKHS~1\AppData\Local\Temp\Engine_14524_d6ed65ebe46645a0993667d9859e36f9_\Engine_6348_3af171cc0b294752841266f5c7ba1f90_.yxdb
Single
Profile
C:\Users\ALEKHS~1\AppData\Local\Temp\Engine_14524_d6ed65ebe46645a0993667d9859e36f9_\Engine_6348_83b58f13b7174513bfe2b56da8129da0_.yxdb
Single
Profile
Source Data
Lookup Table
Vehicle Unit Number: Remove leading zeroes
01234
1234
001234
000001234
01234
1234
1234
1234
001234
1234
000001234
1234
Peoplesoft ID of Assigned Driver: If this ends with SD, remove the SD
Location Code: If more than 8 chars, remove anything after 8. If only 6, add “US” to the front.
C:\Users\ALEKHS~1\AppData\Local\Temp\Engine_14524_d6ed65ebe46645a0993667d9859e36f9_\Engine_6348_81f0abc255cc4ed0833dec104e009894_.yxdb
Single
Profile
C:\Users\ALEKHS~1\AppData\Local\Temp\Engine_14524_d6ed65ebe46645a0993667d9859e36f9_\Engine_6348_bfab391f97f14ed097c52e39300613cc_.yxdb
Single
Profile
C:\Users\ALEKHS~1\AppData\Local\Temp\Engine_14524_d6ed65ebe46645a0993667d9859e36f9_\Engine_6348_37222d1283b449428c023daf246f01b2_.yxdb
Single
Profile
C:\Users\ALEKHS~1\AppData\Local\Temp\Engine_14524_d6ed65ebe46645a0993667d9859e36f9_\Engine_6348_ef732b7ab2ec403c905e51777e970b1f_.yxdb
Single
Profile
Trimmed = Trim([Vehicle Unit Number],'0')
Trimmed = Left([Trimmed], FindString([...
C:\Users\ALEKHS~1\AppData\Local\Temp\Engine_14524_d6ed65ebe46645a0993667d9859e36f9_\Engine_6348_5e38ddfb2bd64b02850b49089af57c74_.yxdb
Single
Profile
Trimmed = REGEX_Replace([Vehicle Unit Number], '^0+','')
Trimmed = REGEX_Replace...
C:\Users\ALEKHS~1\AppData\Local\Temp\Engine_14524_d6ed65ebe46645a0993667d9859e36f9_\Engine_6348_65317c6eeaec44eea8b23592ee4a3027_.yxdb
Single
Profile
Fixed = Left([Location Code], 8)
Fixed = if Length([Fixed])=6 then "US"+[Fixed] ...
C:\Users\ALEKHS~1\AppData\Local\Temp\Engine_14524_d6ed65ebe46645a0993667d9859e36f9_\Engine_6348_b1030fbdca58435ba0d28e018323df11_.yxdb
Single
Profile
Custom
Warning
All
ManualDelayed
C:\Users\ALEKHS~1\AppData\Local\Temp\Engine_14524_d6ed65ebe46645a0993667d9859e36f9_\Engine_6348_7d724b07cc3049e0832e976b0d7b96e7_.yxdb
Single
Profile
Input
Compressed Air Systems and Svc
100
USCL1
150
CAIN2
50
Transport Solution NA EMEA
200
CACL2
250
Club Car
USIN1
Commercial HVAC NA and EMEA
USCL1
Compressed Air Systems and Svc
USIN2
Corporate
USCOR
Fluid Management
USIN3
HVAC and Transport LA
USCL2
HVAC Parts NA and Export
USCL3
Material Handling
USIN4
Parts and Supply Solutions
USCL3
Power Tools
USIN5
Residential HVAC
USCL5
Transport Solution NA EMEA
USCL6
C:\Users\ALEKHS~1\AppData\Local\Temp\Engine_14524_d6ed65ebe46645a0993667d9859e36f9_\Engine_6348_961416bc40e0432eaf1d5c7916323006_.yxdb
C:\Users\ALEKHS~1\AppData\Local\Temp\Engine_14524_d6ed65ebe46645a0993667d9859e36f9_\Engine_6348_18bec74156994608a45d047648f28f20_.yxdb
Source Data
Lookup Table
SBU Code
Description
SBU Code
FindAny
Replace
C:\Users\ALEKHS~1\AppData\Local\Temp\Engine_14524_d6ed65ebe46645a0993667d9859e36f9_\Engine_6348_d0eb70d7ce07439fa28a2fcbe40436ff_.yxdb
Single
Profile
#5 Attempt 1
#5 Attempt 2
Horizontal
35.