C:\Users\vk1720\AppData\Local\Temp\Engine_10500_976c8be949dd4c64b16d8ebb6e14142f_\Engine_11448_0550dafa1d2c4057a0a25f6c363986be_.yxdb
Single
Profile
iVBORw0KGgoAAAANSUhEUgAAAXoAAAA0CAIAAAAL52RvAAAABGdBTUEAALGPC/xhBQAAAAlwSFlzAAAScQAAEnEB89x6jgAAABJ0RVh0U29mdHdhcmUAR3JlZW5zaG90XlUIBQAADRxJREFUeF7tnd+OFccRh/0GzhvEb4DfwH4D+w3sN0jewFxxhxIpcGEJCS4s+WKJsYR9w14kYq8WIWE2CgqKMVqL4FUQ8kIisCVL5NtTvZXa6j/TZ2fm7J51fWqNTtdU93QPU7+pnjNneetNEATBSgi5CYJgRYTcBEGwIk5Gbv7y5D83Hu2nSrAOPHny5O7du7u7u6keBMuzarl5/OLn97/49q0/fUNJpmAdCLkJxrNSubn37NVvPv2baM2ycvPjT7+c395DqhCsZApWSMhNMJ6Vys0n2z+o1iwlN1anWIgla7BCQm6C8ayH3Lz96Y62Crk5EUJugvGsh9zYViE3J0LITTCekJugi5CbYDwhN0EXITfBeEJugi5CboLxhNwEXYTcBOMZkJsff/rlj988+3jz+/e/+Na+MkN59/OH2Nl779mr5D3EycoN4zy/vff72/9iLmz5/OWjF2lfMETITTCeAbn54KvvbKjXyjvXHhDAgy/gLSU3f7j3b3SBgq7ZVmgcSmHLoABdffDcaaUW7PSAqibXEuz965P/Skmmxe8wRLlEvJJ1AbvUv92zok2sdvNZ+0mmCnaEM70GGXITjGdAbj7a3HXx2SiEbmpWYSm5sZ7t8t6f/5naZBCHTq2KBZ+GLljNRQTxRGLUIiW5LrB2TmCy1kEN1f+31/6erEffNvrd7SfJmuHm2HPEYxByE4xnQG6463LRE8+EHGJhCxYbD1LaiQatrHOyVrCe7dKQm3Of/yN3luLsRGxqk2GdmXWeKLkBWHkalGCwg+QUJesiv1M7pXZuUSLrttbZzf7+PocAPiRTk9evX4s/JFOdTjdFBsMhUn3Bsp3s7e25TsTCNtVLFH0GW4GM2Z09LNYo1R7Ef0LGPip2CjKh3ORKUSs2RC3ucFRtCsNnF6i1fnJtcsUlFDce7du97d++ow7W2YmFPQmsWPMUjBOuDpTaFMYzq9wQCTdv3rx8+fIFA9XNzU0X7QLGra2tK1euJNdDsOzs7CSnDPG5detWqjd5+PCh+Lspi5HRpnqT27dvi7/t5LPPPsPCNtVLFH0WPV24f/9+qpeQI7JN9QXSUI1S7UFOwsWLFxsyxy5xvnPnTjLVGSs37nKfUG6UpQ6huDAmU0g7jmIVh3hO1qPkckNah8QwknvPXrHNVYA1kTq3Vzc2hXFZEtC/7qW4JRXHZcy6F21KO2ZgPrkhfuR6LcK1zkWfXBdwfWNMuxcQltaC6BTDI+2+cGEwR4BLly6Jc1FuYPBUoIk6qgnlph3808oNzoOjHXSwnFm5sTpiH4g4iFh1oxQ7d3JDVNsHukVc3pTrkWKF6eqD58lqcCfNjtAdZXBUY5hJbrglLi7sA4gHG0gciyQC+8bGRjId1Rr2Wn9SJHqQvcWYlFYwGBtWAWtyM9iJRL4wodwAelpM+kAOqsoiSCtnVBibOBT/cTmrsreYVemJaiig5czKjX2uVEttBKsmjDBZDdaBbnui2uVWRR0Bm7zQc02Viksqd1qKI5+QOeSGa1Qu1sYdG7sNLV1AuZRHwV8UB89kOgSjSlVjIpqVSILjPBetUz+NdU2jk5FyI93WVnOTyw1In6xtncbpHDvXp3A25ebYo/rgq++S1WDlJl/v1LAaUewWbHrSWHO56ciSyvZPitRIoCZhDrmRoILOe6PeS2uRI2j8ODkQI7HBluBJ1gyJLtxkeG7KB10sBsC20YnkZWzzTopS4ij6YAE9CUWxk4G58yP+tZOmp6vxjyui6XqQY6E4ToYanE25cd/pJGsFO6qimhxPbuzX25SiHNgUrD0vt2768OvHttpzTkYyudxoll67UedIatNzfTfCVe/JxXCVvXII6aQoN9pJMYZlajjwIe+kODZH0QcL8EG0DHKZFglwoxLn4lChR27UhxmJRf/5amlmkbMpN2QKtkmyVphJbtAXbUXJ11P2C6zG0yWB3uxTHlsk2ZmbyeVG79Kd1ysRLv498qSPhFJ9gVoat2WJZInMXCngoItFJzL+Y3RSlBJH0QcL8IEjivLmCxyZmlOWRbtRcgMbGxv4XL9+Xao9s8jpkhuu9S8fvTi/vcdNVd6j1eJeojslcmMFguLG7Ir9cmdCuQGrehwoWQ+xe3skw50HKStYRgmTy40EBnSupDQqamFjKYaQWORzcXUgd2x2SQxLRLkpH3RxtBP35EJTm1onPYFa9MEC8lkfUWn8C3JW3bwW7cbKDfOSI+KmTTTZ6WRAbh6/+Pnjze/dJd4op1Nu+su0cuNewLHv1Ljcx71uU8MtqSgr+w8t5pObVB9CL/Fa2FjU2Q5YLPJZcxMbMHID10VWrhRw0MVhJ3oU24m00pdQ8k6KUuIo+mCBVKk8yZKz6k5R7mYpnqsikjMyKhlezz+EoyU35P+1nxrVyhxy4x6CzCo3xee1x5YbsCsg+wWZndS57vdlnPL2vLI8FfPJTecdUqOi5yovhpBYUuUwpHVpJk1IWKQK4uCmfNBF1ommGD2diIVtqpco+mCBVFkgj71B+5ez6k6R+NTOm4wZ3EyL6DeDmgMuRVVuuN86rSEq0AsKd1Suey3Wh2pqX+J4cuNatQ8hWIFg2DrUwZLaH2WM3NjB299J2D7zxzo1mIu2kkL/ad/MzCc3nX2yfBD/HrmR12HBaplYUsWEmazmarrghrdo8f9OdFTiJk00P4Jat2xTvUTRBwukyiHu8fnccqMndqknxEpVbuwPf9ovm6gbpRaxwonIzfgUYIzcuBdwZNHkjJ0PX9x50DLr233K5HKjF26PfAjyrCR/oSZHbvs2y4DF0Y6Eq6yeiGpZmLjwlph3Uz7o4mgn8mAYZ4nbwU70oKleQlo5HyyQKoegp/JIRU7L3HKzlHNOVW7sBd1+Tc56nkK5oSTrcRkjN2Cby5m039M3Xrex2BcCXWn8uHRCJpcbkDhpvL3ikMAevNa5z0vPulASpG2qLCBWxSj+7qF1rhSwcPed6ETYOv+8E1EEmqR6CRHWomqkikGFG5FdS7khpO0F3Y7wfs+VyY1rMvK7m5FyYx/TyM+y7Jqo81mvbUKy6V4sYr7JbzbmkBuJDahFgkPVgTt548GBqpJdSYEYU+UQffzhtAk65QZ0Ii4fgbwTXX/ZNZdFQ9rJnxhT5Sg6C1lbrbfcJGsF67mU3BxvEdEjN278/Q9HioyUG6ZpX+ez6jP4uo3gxEVSJDsqytxLqjnkRtMQqP2emECyQqCBTVw5NQE6vH79ujjkHYo9VQ6RMUDeW7/cLNuJGPHPzycSg529uXIdHLgiNyB9CustN+0I7/c8hnCAC7aedMB9zZy/87IUI+UG3GuHWnpet3HP7HUA2K2Kzb2kmkNuQKMLWIygERxC4LMsT1wYa/ICiMvW1harCbbYtas8VQHZlSoGxAJSxdAvN7BUJ0xHh8oUdnZ2FjPe3dzcFCN7XWoDsitVMji69rlmcuOeFDSyA/fUs60g9sZO6Xxs4bSvs5WL8DEJzni5cVPQIk+O29ijoy+2STHrmYmZ5AaIK1kCFCFc88BjGSJPN3Kw1xYp4pAqHSwlNzWKnUBj1kwhnzLI3lQpoVqwZnID9s75zrUHeWAgSfkbgNxj5e8Hk1DkEZ4/78RTeiYfkReX80WBUzSK/l1ktv2tcCsu3xqHFsbLDeQ/QTjX8bqNE5T8GY0dG0mQnJY5mE9uBDIUUhKCk1s08cYHllHtw9GEENrY2MCZLZ+xpH0lcINU6YAB4O8if5JOFJSRkYvuMGs+17QSeg7NGcg7kYa1nhmbONQGaVnKOacqN/kLrCiI/AcGH3792L74XyvFb6BrP/zRMvib7GIpLiXc2k0KnjIR+UGGnUhtPTKJ3OSDGUy40A67jCrKk1PVkcvGBnPLTfBroCo33PMHpYGCT0ML8nyhtqzQUhSpPC3KS3I9Su2hSa0U059J5CbPtorHsrgTW0u+nJDNtKQKuQnGU5Ub4PrOX2O1hQyImCGQ7MpLi3vQoHBXL/pLqf1pmBuP9hutGguTPE1rlOKAJ5EbsP0MPoFyz7nQlLSjhP1nmmlJFXITjKclNwLXPRKgmQ4xT9hwC7U3Zz4TP+rD1U+QNy56/OmBbulKOqQQUbUbuFBsxYEGv6tiJAyvoVaMvDFgBiZt2Q6ugBowcj3i4JiZqUxQ5pisFThv6kxpn8bjEXITjGdYbs4SxCFxjnxIIaRZ3M2RC+RwFNUaZCtZ14eQm2A8vy65OUHsmm4wWzmFhNwE4wm5WQUsA+13TKvJp6Yl5CYYT8jNKmDhplrz3oiHzSdIyE0wnpCb2bn37JVNbdovXp9aQm6C8YTczAhrqPPbe1Zr1jS1gZCbYDwhNxODoCAr7x79i/FS3q7/x3Wnn5CbYDwhNxPjJEYLWjPH6zArI+QmGE/IzcQ4lZHy0ebu+uY1QshNMJ6Qm4nR957ZfrL9w9UHz9fxa++cly9fPn36NP/zUUHQT8hNEAQrIuQmCIIVEXITBMFKePPmf4GyDTAL6GK4AAAAAElFTkSuQmCC
Weekly Challenge #262
Clean up and extract the relevant information from the externally generated report. The relevant information is the information below the Receipt line.
C:\Users\vk1720\AppData\Local\Temp\Engine_10500_976c8be949dd4c64b16d8ebb6e14142f_\Engine_11448_0ed6a31f09c6446ea8b8b1933ce8eb6a_.yxdb
Single
Profile
ZZZZ UDRC Report Example Report Date: 02-JUN-2019 20:30:00
Application GL Date From : 01-APR-2019
-------------------- -------- -----------
Customer Billing/Invoice Related Transaction in Transaction Allocated Earned Unearned Total Amount In
Number Customer Batch Number Customer Receipt Number Currency Currency Receipt Amount Discount Discount Functional Currency
--------- -------------------- ---------- --------------- ---------- --------------- ----------- --------------- --------------- ---------- ---------- --------------------
P290 AUDE 06-MAY-2019
"12345678 FAKEF IN22222222 10019777 NON OP(12345) USD 1,220.10 11,022.88 0 0 1,170.10 "
"23456789 AKEF IN33333333 10007999 NON OP(12345) USD 122.40 1,222.74 0 0 132.40 "
"34567890 AK IN4444444 10007999 NON OP(12345) USD 1,222.00 11,222.00 0 0 1,230.00 "
P290
AUDE
06-MAY-2019
C:\Users\vk1720\AppData\Local\Temp\Engine_10500_976c8be949dd4c64b16d8ebb6e14142f_\Engine_11448_5a1d83032e4644d08ef6f3d24492a9b1_.yxdb
Single
Profile
12345678
FAKEF
IN22222222
10019777
NON OP(12345)
USD
1220.1
11022.88
0
0
1170.1
23456789
AKEF
IN33333333
10007999
NON OP(12345)
USD
122.4
1222.74
0
0
132.4
34567890
AK
IN4444444
10007999
NON OP(12345)
USD
1222
11222
0
0
1230
Solution - official for study purposes
Copy value from top Browse into bottom RegEx tool
16-19
21+
Use if you know the rows to keep, there is probably a more dynamic solution
Use if you know the rows to keep, there is probably a more dynamic solution
REGEX_Match([Original Data], '^-{3,}.+')
Custom
Isolate row with dashes, each set of dashes shows the number of characters per column
REGEX_Match([Original Data], '^-{3,}.+')
Replace every set of two consecutive spaces
Original Data
Split where the spaces were previously
Split where the spaces were previously
Calculate column widths
^(.{
}) (.{
})$
Use the summarize tool to generate RegEx expression
Use the summarize tool to generate RegEx expression
Add labels and remove quotes in data lines
Label = IIF(Left([Original Data], 1) = '"', 'Data', 'Heading')
Original Data = R...
False
False
"RegExOut1","RegExOut2","RegExOut3","RegExOut4","RegExOut5","RegExOut6","RegExOut7","RegExOut8","RegExOut9","RegExOut10","RegExOut11","RegExOut12"
False
False
True
True
False
False
False
False
False
upper
Remove extra spaces
Remove extra spaces
Simple
=
Label
True
fixed
2021-03-10 12:27:17
0
Heading
2021-03-10 12:27:17
2021-03-10 12:27:17
[Label] = "Heading"
Ignore
!IsEmpty([Value])
AND
!Contains([Value], '---')
Custom
!IsEmpty([Value])
AND
!Contains([Value], '---')
Concatenate heading rows into one row
Concatenate heading rows into one row
RightInputRows
Ignore
Mapped
Name
Column Names
Rename the data columns
Rename the data columns
Text
ToNumber(ReplaceChar([_CurrentField_], ',', ''))
Possibly unnecessary but this converts the numeric columns to numeric data types
ToNumber(ReplaceChar([_CurrentField_], ',', ''))
C:\Users\vk1720\AppData\Local\Temp\Engine_10500_976c8be949dd4c64b16d8ebb6e14142f_\Engine_11448_c77f4b6007ae49af982fd1ae763e9221_.yxdb
Single
Profile
Customer Table
Customer Table
C:\Users\vk1720\AppData\Local\Temp\Engine_10500_976c8be949dd4c64b16d8ebb6e14142f_\Engine_11448_707eff18f3ab4bbb9b843ce50ff50291_.yxdb
Single
Profile
Copy the RegEx into the RegEx tool below
Copy the RegEx into the RegEx tool below
Original Data
ParseComplex
Original Data
Warn
Original Data_Matched
Paste RegEx expression here
Paste RegEx expression here
Copy value from top Browse into bottom RegEx tool
13-15
20
Use if you know the rows to keep, there is probably a more dynamic solution
Use if you know the rows to keep, there is probably a more dynamic solution
REGEX_Match([Original Data], '^-{3,}.+')
Custom
Isolate row with dashes, each set of dashes shows the number of characters per column
REGEX_Match([Original Data], '^-{3,}.+')
Replace every set of two consecutive spaces
Original Data
Split where the spaces were previously
Split where the spaces were previously
Calculate column widths
^(.{
}) (.{
})$
Use the summarize tool to generate RegEx expression
Use the summarize tool to generate RegEx expression
Add labels and remove quotes in data lines
Label = //IIF(Left([Original Data], 1) = '"', 'Data', 'Heading')
IIF(StartsWith(...
False
False
"RegExOut1","RegExOut2","RegExOut3"
False
False
True
True
False
False
False
False
False
upper
Remove extra spaces
Remove extra spaces
Simple
=
Label
True
fixed
2021-03-10 12:27:17
0
Heading
2021-03-10 12:27:17
2021-03-10 12:27:17
[Label] = "Heading"
Ignore
!IsEmpty([Value])
AND
!Contains([Value], '---')
Custom
!IsEmpty([Value])
AND
!Contains([Value], '---')
Concatenate heading rows into one row
Concatenate heading rows into one row
RightInputRows
Ignore
Mapped
Name
Column Names
Rename the data columns
Rename the data columns
C:\Users\vk1720\AppData\Local\Temp\Engine_10500_976c8be949dd4c64b16d8ebb6e14142f_\Engine_11448_c319eeb8083e4647bea7a2ac4ca0147a_.yxdb
Single
Profile
Copy the RegEx into the RegEx tool below
Copy the RegEx into the RegEx tool below
Original Data
ParseComplex
Original Data
Warn
Original Data_Matched
Paste RegEx expression here
Paste RegEx expression here
C:\Users\vk1720\AppData\Local\Temp\Engine_10500_976c8be949dd4c64b16d8ebb6e14142f_\Engine_11448_1040e93ed76b41b489e546977b5a888a_.yxdb
Single
Profile
Company Table
Company Table
Solution - my Own
14
20
C:\Users\vk1720\AppData\Local\Temp\Engine_10500_976c8be949dd4c64b16d8ebb6e14142f_\Engine_11448_0efad397537d4654bb8780ca880bbd3f_.yxdb
Single
Profile
C:\Users\vk1720\AppData\Local\Temp\Engine_10500_976c8be949dd4c64b16d8ebb6e14142f_\Engine_11448_107ee7c86b99418a961613e21b4b632c_.yxdb
Single
Profile
Original Data
Last
False
True
"Original Data","Blank"
True
True
True
True
False
False
False
False
False
upper
0
21-23
Original Data
ParseComplex
Original Data
Warn
Original Data_Matched
False
True
"Original Data"
False
False
True
True
False
False
False
False
False
upper
Customer Number = [RegExOut1]
Customer = [RegExOut2]
Batch = Null()
Billing/Invo...
Alteryx Educational Asset - Terms and Conditions
Use of this Alteryx asset is for educational purposes only and bound by the conditions set forth on alteryx.com/terms-and-conditions. Alteryx Information may not be reproduced, transmitted or distributed without Alteryx, Inc.'s permission.”
Horizontal
challenge_262_start_file