PART ONE PIXYSTEMS TOY CO., INC. INVENTORY COUNTS: Objective: we have two invent
Place your order now for a similar assignment and have exceptional work written by our team of experts, At affordable rates
For This or a Similar Paper Click To Order Now
PART ONE
PIXYSTEMS TOY CO., INC. INVENTORY COUNTS:
Objective: we have two inventory count files:
● Inventory Count file from the Pixystems Toy Co. Inc. 4 locations
● Inventory Count file from Favorite Toys, Inc. 2 locations (gained in an acquisition
several years ago and still using their own inventory count reports).
We are looking to combine the reports into one inventory count report in order to complete
some analysis. While we could do this through manually copy/pasting, you will note that
the report formats differ slightly with columns in different order, etc. A manual process,
which would have to be completed every period, would be not only time consuming but
would allow for user error. In addition, these files are intentionally small for purposes of
effective demonstration. Actual client reports would be much larger with many more
transactions, inventory items, etc. and therefore, would be much more cumbersome to
manipulate manually to combine files. By using a tool like Alteryx and creating a workflow,
each time we need to combine these files, we simply input the new files and run the
workflow to get our desired output file.
Within Alteryx, to begin building the workflow:
1. Input Data and Input Data Configuration: Click on your ‘Favorites’ or the ‘In/Out’
in the tool bar, and drag the tool called ‘Input Data’ onto the canvas two times.
a. Because we are joining two different data sources, we are building out two
streams with the purpose of combining them.
b. There will be a “top” or “upper” flow, and a “bottom” or “lower” flow when
distinguishing between the two flows.
c. Note as you are loading into Alteryx, the file you are connecting can not
also be open on your computer. If you opened the files in excel to look at
them, please be sure to close them before trying to load into Alteryx.
d. In the upper flow, click on the input data tool and in the configure panel,
connect to the “Inventory Count files PTCI.xlsx” file on their computers and
select the “PTCI” sheet.
e. In the lower flow, click on the input data tool and in the configure panel,
connect to the “Inventory Count files FTI.xlsx” file on their computers and
select the “FTI sheet.
f. Run the workflow as this will allow the data to pass into the tool so the
users can see the data, and what the next step should be. Select either of
the output anchors and look at the results window.
g. The data will have several nulls and the next step will be to clean out that
data. Some columns will have red, meaning they have errors that need to
be fixed.
2. Sample tool configuration: To eliminate the report headers across the top, we
will use the Sample tool. Dragging the Sample tool near the input tool should
automatically connect the two tools. If you do not see the dotted line, drag the
output anchor from the input tool to the input anchor of the sample tool to create
the connection.
a. For both tools, have the settings in the configuration panel set to “Skip 1st
N rows” and N=3, then run the workflow and look at the output.
b. You will see that the blank lines are gone, but you will notice that the
column headers still aren’t correct as they are appearing as F2, F3, etc.
3. Dynamic Rename configuration: To have the column headers appropriately
reflecting the field names, we will use the Dynamic Rename tool which is in the
‘Developer” section (scroll right to find the Developer section or look for Dynamic
Rename in the search bar). Once you find developer and dynamic rename, drag it
down twice (once for each stream).
a. Highlight the Dynamic Rename tool in the top flow and you will see in the
configuration panel in the Rename Mode section that you have the
following choices:
i. Formula
ii.
iii.
iv. Add Prefix/Suffix
Remove Prefix/Suffix
Take Field Names from First Row of Data
v. Take Field Names from Right Input Metadata
vi. Take Field Names from Right Input Rows
vii. Take Field Descriptions from Right Input Metadata
viii. Take Field Descriptions from Right Input Rows
b. Select ‘Take Field Names from First Row of Data”. Repeat this step for the
lower flow and then run the workflow and look at the output.
c. We have now removed all blanks & have our fields appropriately named so
are ready to begin identifying the information we will combine into our
output file.
4. Select tool configuration: Drag down a ‘Select’ tool for each workflow stream,
and connect it to the dynamic rename tool
a. Click the select tool from the upper stream and observe the configuration
panel. When working with a large data file with many columns of
information not needed for analysis, you can uncheck any fields you do not
want to include in the output. In the Field column, remove the check from
the “Inventory Type” Field as we do not need to carry that column forward
in the data file.
b. Note the Type column which indicates the data type for each field.
Numeric values are typically always going to be a “double” while text
values will be a “v_string” as they encompass a full plethora of metadata.
c. The values in the Fields for Minimum Quantity, Quantity Counted, Quantity
Expected and Difference are numbers so should be changed to “double”.
The Item # field should remain a v_string (even though it is populated with
numbers) as it is an identifier & mathematical functions will not be
performed.
d. Note: It is also key to note that you can change the data type of multiple
fields by selecting all of them at once by clicking on one of the fields so that
it is highlighted, then clicking ‘shift’ and finally clicking the last field you
want to change to highlight them all.
e. The Select tool can also be used to change the order of the columns. Let’s
move the Field for Location by selecting it and clicking on the up arrow to
move it to directly underneath Date of Count. Then do the same to move
up the Detailed Location to follow Location.
f. Configure the Select tool for the lower stream, updating the Type for
Minimum Quantity, Quantity Counted, Quantity Expected and Difference to
“double” and confirm the Fields are in the correct order. Run the workflow
and review the results pane.
5. Data Cleansing configuration:
a. Click on ‘Preparation’ in the tool bar, and drag ‘Data Cleansing’ onto the
canvas near the select tool for each flow.
b. In each flow, in the configuration panel for data cleansing, make sure all the
columns are selected, as well as the cleansing items that should be applied
(nulls replaced with blanks, replaced with 0, and leading and trailing
whitespace)
i. This will help eliminate any accidental extra spaces. Doing this in
excel would require adding additional columns and creating a
formula for each cell. It would take a long time to do this by hand.
Data cleansing can also be used to eliminate all whitespace, letters,
ii.
numbers and punctuation, or to modify the case lettering
(upper/lower/title case).
6. Union tool configuration: Now that the data is ready, drag a ‘union’ tool onto the
canvas to combine the two data files. From the right anchor on the Data Cleansing
tools in both workflows, connect to the left anchor on the Union tool. Run the
workflow and review the results pane to see that the data from both files now
appears in the output view.
7. Table tool: Preparing the data for an output file: Select ‘Reporting’ in the tool
bar, and then drag the ‘Table’ tool onto the canvas and connect it to the Union tool.
a. Note: The basic and initial setup is all that will be needed for this tool. We
have the ability to configure the table with different formatting options, but
we will keep the default settings.
b. Run the workflow and select the output anchor of the table tool. There
should not be any data in the results window, but rather we will see the
words ‘Table – View Browse Tool Report Tab’. The data is organized in a
table but we need a browse tool to view it in the Alteryx workflow.
8. Browse Tool configuration: Go back to the favorites section in the tool bar, drag
‘Browse’ onto the canvas. This tool can also be accessed in the ‘In/Out’ section of
the tool bar if it is not in the favorites tool category. Connect the Browse tool to the
Table tool and run the workflow.
a. Click on the browse tool so you will be able to see the data in the
configuration panel. The combination of the table tool and the browse tool
cleans up the data into a more readable format but it is not yet in an output
file for the user.
9. Render Tool configuration: In order to solve this problem, select ‘Reporting’ in
the tool bar, and drag down the ‘Render’ tool, connecting it to the table tool.
Change the ‘Output Mode’ type to ‘Temporary Excel 2007 Spreadsheet’, and then
run the workflow.
a. Select the render tool, and in the results panel, click on the URL next to
the render line. This will open up the temporary file, and the format will
match what is visible in the browse tool.
b. Change the ‘Output Mode’ type to ‘Temporary Excel 2007 Spreadsheet’,
and then run the workflow.
c. In the results pane, in the Messages section, click on the URL to open the
excel spreadsheet which now looks like the input file for the Tableau
Pixystems Toy Co. Inc. case study.
10. Saving your workflow: To save your workflow, select File, Save As to save the
workflow to the desired folder. The workflow will save with a .yxmd file name.
11. Saving your workflow with data files: To share the workflow and corresponding
data files with a reviewer, etc., you will export the workflow as a package. In the
Options menu, select “export workflow” and save the package with the .yxzp file
name.PART TWO
PIXYSTEMS TOY CO., INC. PAYROLL:
Objective: Combine the employee data and payroll files to recalculate the total pay of the
HOURLY employees. The CFO requested that we take a closer look at the payroll
process as employee data and payroll calculations are maintained in separate systems. In
order to recalculate payroll, it will be helpful to combine the employee data with the current
payroll calculated numbers. A manual process, which would have to be completed every
period, would be not only time consuming but would allow for user error. In addition, these
files are intentionally small for purposes of effective demonstration. Actual client reports
would be larger and therefore, would be much more cumbersome to manipulate manually
to combine files. By using a tool like Alteryx and creating a workflow, each time we need
to combine and recalculate these totals, we simply input the new files and run the
workflow to get our desired output file.
Within Alteryx, to begin building the workflow:
1. Input Data and Input Data Configuration: Click on your ‘Favorites’ or the ‘In/Out’
in the toolbar, and drag the tool called ‘Input Data’ onto the canvas two times.
a. Because we are joining two different data sources, we are building out two
streams with the purpose of combining them.
b. There will be a “top” or “upper” flow, and a “bottom” or “lower” flow when
distinguishing between the two flows.
c. Note as you are loading into Alteryx, the file you are connecting can not
also be open on your computer. If you opened the files in excel to look at
them, please be sure to close them before trying to load into Alteryx.
d. In the upper flow, click on the input data tool and in the configure panel,
connect to the “Employee Data.xlsx” file on your computer and select
“Sheet 1”.
e. In the lower flow, click on the input data tool and in the configure panel,
connect to the “Payroll.csv” file on your computer.
f. Run the workflow as this will allow the data to pass into the tool so the
users can see the data, and what the next step should be. Select either of
the output anchors and look at the results window.
g. The data will have several nulls and the next step will be to clean out that
data. Some columns will have red, meaning they have errors that need to
be fixed.
2. Auto Field configuration: To make sure the field types are updated in the Payroll
CSV file, we will use the Auto Field tool. Use the search function to find the Auto
Field tool and then drag it near the input tool for the “Payroll.csv” file and it should
automatically connect the two tools. If you do not see the dotted line, drag the
output anchor from the input tool to the input anchor of the sample tool to create
the connection. You will not need to do additional configurations.
3. Data Cleansing configuration:
a. Click on ‘Preparation’ in the tool bar, and drag ‘Data Cleansing’ onto the
canvas near the select tool for each flow.
b. In each flow, in the configuration panel for data cleansing, make sure all the
columns are selected, as well as the cleansing items that should be applied
(nulls replaced with blanks, replaced with 0, and leading and trailing
whitespace)
i. This will help eliminate any accidental extra spaces. Doing this in
excel would require adding additional columns and creating a
formula for each cell. It would take a long time to do this by hand.
Data cleansing can also be used to eliminate all whitespace, letters,
ii.
numbers and punctuation, or to modify the case lettering
(upper/lower/title case).
4. Formula configuration: To remove the “$” and “,” from the Payroll.csv file, we will
use the Formula tool found in the Preparation section. We need to remove these
symbols as Alteryx does not recognize them as the correct field type when they
exist with the numbers. Once you find the Formula tool, drag it down to the
“Payroll.csv” stream. (Please note that the RegEx tool in the Parse section can
also be used for this).
a. You will create 4 formulas in the configuration panel. In the configuration
panel first select “Pay” in the Output column. Then type in the following
formula:
i. ToNumber(REGEX_Replace([Pay], “$|,|”, “”))
ii. This will replace all “$” and “,” with blanks
b. In the configuration panel, click on the plus sign below the first formula to
add your next formula. Select “Savings” in the Output column. Then type in
the following formula:
i. ToNumber(REGEX_Replace([Savings], “$|,|”, “”))
ii. This will replace all “$” and “,” with blanks
c. In the configuration panel, click on the plus sign below the second formula
to add your third formula. Select “Tax (10%)” in the Output column. Then
type in the following formula:
i. ToNumber(REGEX_Replace([Tax (10%)], “$|,|”, “”))
ii. This will replace all “$” and “,” with blanks
d. In the configuration panel, click on the plus sign below the third formula to
add your final formula. Select “Total Pay” in the Output column. Then type
in the following formula:
i. ToNumber(REGEX_Replace([Total Pay)], “$|,|”, “”))
ii. This will replace all “$” and “,” with blanks
5. Auto Field configuration: To correct the field types now that the “$” and “,” are
removed, add the Auto Field tool to the “Payroll.csv” stream after the Formula tool.
You will not need to do additional configuration with this tool.
6. Join tool configuration: Now that the data is ready, drag a Join tool onto the
canvas to combine the two data files. Connect the “Employee Data” stream to the
left anchor and the “Payroll” stream to the right anchor. Run the workflow and
review the results pane to see that the data from both files now appears in the
output view.
7. Filter tool configuration: We have been asked to recalculate the payroll for
HOURLY employees only. To remove the salary employees from the data, select
the Filter tool from the Preparation section. In the configuration panel, use the
“Basic filter” option. Select “Type” for the column header, “Equals”, “Hourly”.
8. Select tool configuration: Drag down a Select tool and connect to the T (true)
anchor from the Filter tool.
a. Click the Select tool and observe the configuration panel. When working
with a large data file with many columns of information not needed for
analysis, you can uncheck any fields you do not want to include in the
output. In the Field column, remove the check from the “Salary Amount”,
“Bonus”, “Year End Bonus” and “Unknown” fields as we do not need to
carry these columns forward in the data file.
b. Note the Type column which indicates the data type for each field.
Numeric values are typically always going to be a “double” while text
values will be a “v_string” as they encompass a full plethora of metadata.
c. The Select tool can also be used to change the order of the columns. Feel
free to adjust these columns as needed.
9. Formula tool: Recalculating Payroll for Hourly employees: Select the Formula
tool in the preparation section, and then drag the ‘Formula’ tool onto the canvas
and connect it to the Select tool. In order to recalculate the total pay, we also need
to recalculate the pay, savings, and tax.
a. Recalculated Pay: For the Output Column in the configuration panel, add a
new column and name it Recalculated Pay. For the formula, input the
following:
i. IF [Hours Worked]<=80 THEN [Hours Worked]*[Hourly Amount]
ELSE 80*[Hourly Amount]+([Hours Worked]-80)*([Hourly
Amount]*1.5) ENDIF
b. Recalculated Savings: In the configuration panel, click on the plus sign
below the first formula to add your next formula. In the Output Column add
a new column and name it Recalculated Savings. For the formula, input the
following:
i. [Recalculated Pay]*[Savings Plan %]
c. Recalculated Tax: In the configuration panel, click on the plus sign below
the second formula. In the Output Column add a new column and name it
Recalculated Tax. For the formula, input the following:
i. [Recalculated Pay]*.1
d. Recalculated Total Pay: In the configuration panel, click on the plus sign
below the third formula to add another formula. In the Output Column add a
new column and name it Recalculated Total Pay. For the formula, input the
following:
i. [Recalculated Pay]-[Recalculated Savings]-[Recalculated
Tax]-[Medical]
e. Total Pay Difference: To check if there are any differences between the
total pay stated by the company and your recalculated total, you will want
to add this final formula. Click on the plus sign and add a new column in
the Output Column drop down. Name this Total Pay Difference. For the
formula, input the following:
i. [Recalculated Total Pay]-[Total Pay]ii. Note that there will be some discrepancies as there were errors in
the original calculations
10. Output tool configuration: To create an output of your file, select Output Data in
the In/Out section and drag it down to the Formula tool.
a. Select where you would like your output file to be housed on your computer
by selecting the drop down arrow in the “Write to File or Database” portion
of the configuration panel.
b. Once the Data connections window opens, select Files on the left side of
the pane and then “Select file” in the top right corner.
c. Navigate to where you would like to save this file on your computer and
then create a new document with the title “Payroll Recalculation Output”.
Also make sure to select the file type to be Microsoft Excel (.xlsx)
11. Saving your workflow: To save your workflow, select File, Save As to save the
workflow to the desired folder. The workflow will save with a .yxmd file name.
12. Saving your workflow with data files: To share the workflow and corresponding
data files with a reviewer, etc., you will export the workflow as a package. In the
Options menu, select “export workflow” and save the package with the .yxzp file
name.
