As the vast majority of processes are run on a periodic basis, it is important to clear out the redundant data from previous periods to produce a clean and correct report. This article will subsequently outline how to build a process which clears out the redundant data from the previous month without threatening the data integrity of the managed tables.
1. Building the Date Tagging Process
Suppose that we are building a process which processes the data in a sample input file to produce reports every month.
Snapshot of Sample Input
We will now need to tag each entry with the appropriate time stamp. In order to achieve this, a set of drop down menus containing "Month" and "Year" values must be constructed.
A. Configuring the Data Steps
- Edit the data step that contains the process inputs, and add two lists to the step. Change the list names to “Month” and “Year”.
- In the “Available values” field, click on the “(Collection)” drop-down. Add in the appropriate data in the “Month” and “Year” drop-down menus as shown below.
- Select the desired date values and save the step to store these values into the database.
Add an empty worksheet in the output template and call it “Configuration”.
Run the process so the “Configuration” worksheet will be reproduced in the working copy.
B. Configuring the Date Transformation Process
Create a “Multiple file manipulations” action step and configure two “Update Cell” to print the chosen Month and Year into the configuration sheet. To link the drop-down values, click on “Input\Output of another step” and select the appropriate input.
- Add a “Month Lookup” table and two formulas which transform the selected "Year" and "Month" into a numerical date. This date will be used to tag each individual entry with the correct date.
The formula that calculates the previous month is:
=DATE($C$6, VLOOKUP($C$5, $E:$F,2, FALSE), 0)
- The formula that calculates the current month is:
=EOMONTH(DATE($C$6, VLOOKUP($C$5, $E:$F,2, FALSE), 0), 1)
- If we then run the process for May 2016, this spreadsheet will produce the dates:
- Edit the Input data step and add two “Date” portals from the Property Toolbox.
- Add two "Date" boxes in a data step. Name them "Month End" and "Year End".
- Add two “Publish Cell” instructions. These should be appropriately linked to the "Date" boxes constructed in the previous step.
- Run the process so the dates are stored in SolveXia.
- Add an “Insert Column” step which inserts one column at “A”.
- Create an “Update cell value” step to tag all of the data within the KPI worksheet with the current date. This can again be linked using the “Input\Output of another step” feature.
2. Building the Managed Tables
We must firstly create and name our managed table. Once we have completed this, we need to add the rows required to store the processed information. In the case of the sample input, we need to add six rows along with their name and their data types.
3. Importing Inputs into Process
After constructing the managed table, we need to create a step which populates the managed table with the data within the relevant spreadsheets.
- Firstly, add a “Multiple file Manipulations” action step to the process.
- Select the “Import from Excel” (or “Import from CSV” if the data is stored in a CSV) instruction.
- Select the input and the range of cells that will imported into the target managed table as shown below. We are extracting from the address A2 to ensure that the headings in the workbook are not being extracted into the managed tables.
4. After running this process, right click on the “Sales_Data” button shown in the image below:
5. Click on “View top 20 rows” to make sure that the data has been imported into the managed tables as expected. In the case of the “Sales_Data” example, the following results should have been obtained:
4. Clearing Redundant Data
Many processes will need to be cleared before the start of a new reporting run to prevent data redundancy. This can be implemented in SolveXia through adding the “Run Query” instruction within the “Multiple File Manipulations” step.
- Click on the “Insert Field” option (1) to write a delete query where the date changes dynamically every time the process is run. After clicking the “Insert Field” option, click on “Click here to choose a step property” (2) and select the current date from the options.
2. After selecting the date, we get:
This step needs to be placed before the populating steps in part 3 to ensure that there is no data from the current month within the table before we import the current month’s data. Otherwise, the table could potentially contain data duplicated from previous runs.
5. Importing Previous Month’s Data
We finally need to write up a step which exports the data from the previous month into the output workbook. If any important data from the previous month is cleared from the managed tables, a copy of the data will remain within the output workbook. This step should be placed afterthe populating steps in part 3.
- Firstly, add a “Previous Sales Month” worksheet to the output template.
- Add a “Clear cell range” step which clears out all of the data within the “Previous Sales Month” worksheet.
- Add a “Select to Excel” instruction within a “Multiple manipulations step”.
- Write up a select query which selects all of the data from the previous month. Click on the “Insert Field” option and select the input with the date of the previous month.
After completing these steps, save and run the process. This should produce a managed table with the current month’s data without the redundant data from the previous month.