9.1 File Splitting

Alteryx Essentials Workshop 1 - File Splitting & Consolidation
5 minutes
Share the link to this page
Copied
  Completed
You need to have access to the item to view this lesson.
One-time Fee
$49.99
List Price:  $69.99
You save:  $20
€47.77
List Price:  €66.88
You save:  €19.11
£39.64
List Price:  £55.51
You save:  £15.86
CA$70.94
List Price:  CA$99.32
You save:  CA$28.38
A$78.59
List Price:  A$110.04
You save:  A$31.44
S$67.09
List Price:  S$93.93
You save:  S$26.84
HK$388.70
List Price:  HK$544.21
You save:  HK$155.51
CHF 45.01
List Price:  CHF 63.01
You save:  CHF 18
NOK kr556.49
List Price:  NOK kr779.13
You save:  NOK kr222.64
DKK kr356.31
List Price:  DKK kr498.87
You save:  DKK kr142.55
NZ$87.54
List Price:  NZ$122.57
You save:  NZ$35.02
د.إ183.61
List Price:  د.إ257.07
You save:  د.إ73.45
৳6,082.62
List Price:  ৳8,516.16
You save:  ৳2,433.53
₹4,345.36
List Price:  ₹6,083.86
You save:  ₹1,738.49
RM222.23
List Price:  RM311.14
You save:  RM88.91
₦75,492.39
List Price:  ₦105,695.39
You save:  ₦30,203
₨13,978.18
List Price:  ₨19,570.57
You save:  ₨5,592.39
฿1,684.21
List Price:  ฿2,358.03
You save:  ฿673.82
₺1,813.34
List Price:  ₺2,538.83
You save:  ₺725.48
B$286.22
List Price:  B$400.73
You save:  B$114.51
R918.92
List Price:  R1,286.57
You save:  R367.64
Лв93.45
List Price:  Лв130.83
You save:  Лв37.38
₩72,018.15
List Price:  ₩100,831.17
You save:  ₩28,813.02
₪178.28
List Price:  ₪249.61
You save:  ₪71.32
₱2,907.99
List Price:  ₱4,071.42
You save:  ₱1,163.43
¥7,590.87
List Price:  ¥10,627.83
You save:  ¥3,036.95
MX$1,012.87
List Price:  MX$1,418.09
You save:  MX$405.22
QR182.48
List Price:  QR255.49
You save:  QR73
P691.04
List Price:  P967.51
You save:  P276.47
KSh6,461.20
List Price:  KSh9,046.20
You save:  KSh2,585
E£2,529.99
List Price:  E£3,542.20
You save:  E£1,012.20
ብር6,426.16
List Price:  ብር8,997.14
You save:  ብር2,570.97
Kz45,590.88
List Price:  Kz63,830.88
You save:  Kz18,240
CLP$47,388.02
List Price:  CLP$66,347.02
You save:  CLP$18,959
CN¥363.92
List Price:  CN¥509.52
You save:  CN¥145.59
RD$3,107.31
List Price:  RD$4,350.49
You save:  RD$1,243.17
DA6,740.10
List Price:  DA9,436.68
You save:  DA2,696.58
FJ$114.89
List Price:  FJ$160.86
You save:  FJ$45.96
Q386.25
List Price:  Q540.79
You save:  Q154.53
GY$10,474.43
List Price:  GY$14,665.04
You save:  GY$4,190.61
ISK kr7,008.09
List Price:  ISK kr9,811.89
You save:  ISK kr2,803.80
DH498.75
List Price:  DH698.29
You save:  DH199.54
L932.39
List Price:  L1,305.42
You save:  L373.03
ден2,939.32
List Price:  ден4,115.28
You save:  ден1,175.96
MOP$401.15
List Price:  MOP$561.64
You save:  MOP$160.49
N$922.85
List Price:  N$1,292.07
You save:  N$369.21
C$1,842.14
List Price:  C$2,579.14
You save:  C$737
रु6,955.59
List Price:  रु9,738.38
You save:  रु2,782.79
S/185.20
List Price:  S/259.30
You save:  S/74.09
K201.36
List Price:  K281.92
You save:  K80.56
SAR187.48
List Price:  SAR262.49
You save:  SAR75.01
ZK1,408.06
List Price:  ZK1,971.40
You save:  ZK563.33
L237.76
List Price:  L332.89
You save:  L95.12
Kč1,197.96
List Price:  Kč1,677.24
You save:  Kč479.28
Ft19,190.82
List Price:  Ft26,868.69
You save:  Ft7,677.86
SEK kr535.66
List Price:  SEK kr749.97
You save:  SEK kr214.31
ARS$52,958.15
List Price:  ARS$74,145.65
You save:  ARS$21,187.50
Bs347.18
List Price:  Bs486.09
You save:  Bs138.90
COP$206,555.21
List Price:  COP$289,193.83
You save:  COP$82,638.61
₡25,356.42
List Price:  ₡35,501.02
You save:  ₡10,144.59
L1,276.31
List Price:  L1,786.93
You save:  L510.62
₲395,669.61
List Price:  ₲553,969.11
You save:  ₲158,299.50
$U2,162.01
List Price:  $U3,026.99
You save:  $U864.97
zł198.63
List Price:  zł278.10
You save:  zł79.46
Already have an account? Log In

Transcript

So following on from the previous video, HR have kindly provided us the file of salary details. In the fall we have employee ID, their first and last name and their current position. We also have the business unit code, business unit description and current salary. Our manager has asked us to split this file into individual workbooks by the business unit code column. Each file will be sent to one of the senior managers in that business unit to fill in the forecasted spend. So at the end of this, we should end up with eight different workbooks.

We also need to include four new columns at the end of the file to forecast whether each employee will get a pay rise commission bonus and fringe benefits. So for step one, let's start by importing our HR file into our workflow. We'll go to the in house tab and drag in the input data tool and connect to our HR file 9.1 Hr file worksheet. For step two, we need to add four new columns to the end of our file. We'll do that by adding a formula tool into our workflow. So let's go to the preparation tab and drag in Formula.

Let's go to the Select column drop down to the left and add pay rise as one of the columns that we needed to add. We'll give this a data type of double with a size of eight and assign it a value of no as we want the senior manager in that business unit to fill this in. Will do There's three more times for commission bonus and fringe benefits. commission. Bonus and fringe benefits, Double, double, and double. For step three, we'll add one more column called file, and this will be the location of where we store our spreadsheets.

We'll give this a format of a W string, leave the size alone and type the folder location in double quotes along with the file name and sheet name. So I'll put mine in C Drive, educate workshop one, workshop one dot XLS x sheet, sheet one. Now here's the important part. Because we want to split the sheets by business unit, we're going to add the business unit code, in other words, be a one or two etc, into the filename. So we'll break up the text here with a double quote and we'll add the BU code in here. Bu code plus XLS x.

And for sheet name will also put the code. Let's add a browse tool with Ctrl, Shift V, and run our workflow. And we've successfully added our four columns here, and our new file column. Okay, so the final step is to export all of this into eight different spreadsheets. Let's start by dragging the output data tool into our workflow and connecting that to the formula tool. In the configuration pane, we'll go to file and save this as a spreadsheet.

We'll give this an arbitrary name. It's not going to matter because it will be overwritten by the file name that we created in the formula tool. Under output options, we'll select overwrite file if it already finds an existing one, and under take file table name from field. We're going to change the entire file path from here, and we're going to replace it with the file column we created in the formula tool. Once we've done that, we can run our workflow. And if we go to this folder here, we will verify if our records were written successfully.

And then we have our HR file has been split into eight different files, and it's ready to send to our senior managers to fill in

Sign Up

Share

Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.