OverviewBuild historical data is one of the widely used instructions from library of SolveXia’s process automation. This instruction is used to build historic data set in MS Excel file based on provided keys and period identifier. It can copies new or replace the existing data in the historic data set based specified period identifier in the instruction. It uses source and target MS Excel files. Source and target files could be same or different, however source and target worksheets must be different.
Below image shows an example of the data in source worksheet and target worksheet before running Build historical data instruction.
Data in source worksheet – Column A contains unique keys and column B to M contain month wise data, month as period identifier.
Data in the target worksheet – Column A is key columns and contain some of the keys from source worksheet. Column B to E contain month wise data, month as period identifier.
Below steps will guide you to configure Build historical data to copy data from source worksheet to target worksheet based on period identifier and matching keys for above data. File names, worksheet names, range addresses, key columns and period identifiers could be different for other examples.
1. Open a process and click on Design mode button from the tool bar.
2. Add / open a file manipulation step.
3. Click on ‘Click here to select an instruction’, select Build historical data instruction from Excel group.
4. Click on Link button on the source file to link a source file.
5. Select ‘ source worksheet from the Worksheets drop down list.
6. Select a range of one column from Address- C2 to Last cell in column-C, targeting column-C to copy to build historic data.
7. Click on Link button on the target file to link a target file – if target file is different than source file. Otherwise, click on Make same as source to link same source file in the target.
8. Select Target target worksheet from the Worksheets drop down list.
9. Enter the cell address as A1. Target cell address generally starts from one row above the key column.
10. Enter the column name –A for source key column.
11. Enter Feb as the period identifier. Period identifiers are column headings in the target worksheet. You can also link to other step’s property as period identifier. Tick the Case Sensitive? – if you like to check against case sensitivity.
12. Choose Replace option for ‘If period already exist’, Grow option for ‘If new keys are found’, Right option for New period added to and Unlimited for Limits of periods (Detailed description of other options is available below in this article)
Completed instruction should look as shown in below image.
13. Save and run the step.
14. View target file.
After running this instruction, output data in target worksheet should look as shown in below image. In this image – (1) data from column C of source worksheet copied in Feb column by period identifier (2) new keys added in target file as highlighted with red border.
Parameters - Build historical data instruction has parameters as shown in image below. These parameters let you customize Build historical data instruction for specific needs. Each parameter is explained below on what it does.
If period already exists –Replace – This option replaces the data in the in the column where specified period is found Fail – This option fails the step – if specified period is found in the target worksheet. Replace and issue warning – This option replace the data in the in the column where specified period is found – and complete the step with warnings
If new keys found– Following options are for key column in the source excel file – instruction checks the target file while running the and make the decision based on specified parametersGrow – This option add the new keys in the target worksheet. For example, there are 5 keys in the source worksheet and only 4 rows in the target worksheet. Selecting this option copies 5th row key with the data in the target file. Fail – This option fails the step if any new keys are found while running the step. It means – source and target keys must be matched in order to run step successfully. Ignore – This option ignores any new keys that are found, and only copies the historic data by matching the keys that are exist in the target worksheet.
New period added to, – This option is to decide which way you like to move your historic data when specified period does not exist in the target file and instruction attempts to add new period column.Left – This option adds column at the beginning of the data range after key column and enter the period identifier with data. Right – This options add new column and copies the data at the end of the existing data range in the target worksheet.
Limits of period – This group of options allow you to limit the numbers of period in the target worksheet.Unlimited– This option lets you copy unlimited numbers of column until you reach the limitation of selected Excel file. Limit to – This option allows you to select the number of columns you wish to limit for historic data in the target worksheet.
Limitations of Build historical data instruction
- It can copy only 1 column of data per instruction,
- Source or target cannot have duplicate keys,
- Target worksheet cannot have duplicate period identifier