How to use build historical data

Overview

[highlight txt=’Build 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.

[message type=”warning”]For the purpose of this article we are going to use a simple and fictitious example of a source and target excel files. You will want to replace reference to the source & target excel files with files of your own.[/message]

Below image shows an example of the data in source worksheet and target worksheet before running [highlight txt=’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 [highlight txt=’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.

Steps

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 [highlight txt=’Build historical data’] instruction from Excel group.

4. Click on [highlight txt=’Link’] button on the source file to link a source file.

5. Select [highlight txt=’Source – Numeric]’ source worksheet from the Worksheets drop down list.

6. Select a range of one column from Address- [highlight txt=’C2′] to Last cell in column-[highlight txt=’C’], targeting column-[highlight txt=’C’] to copy to build historic data.

7. Click on [highlight txt=’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 [highlight txt=’Target’] target worksheet from the Worksheets drop down list.

9. Enter the cell address as [highlight txt=’A1′]. Target cell address generally starts from one row above the key column.

10. Enter the column name -[highlight txt=’A’] for source key column.

11. Enter [highlight txt=’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 [highlight txt=’Replace’] option for ‘If period already exist’, [highlight txt=’Grow’] option for ‘If new keys are found’,  [highlight txt=’Right’] option for [highlight txt=’New period added to’] and [highlight txt=’Unlimited’] for [highlight txt=’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 [highlight txt=’Feb’] column by period identifier (2) new keys added in target file as highlighted with red border.

[highlight txt=’Parameters -‘] [highlight txt=’Build historical data’] instruction has parameters as shown in image below. These parameters let you customize [highlight txt=’Build historical data’] instruction for specific needs. Each parameter is explained below on what it does.

[highlight txt=’If period already exists’]

[highlight txt=’Replace’] – This option replaces the data in the in the column where specified period is found

[highlight txt=’Fail’] – This option fails the step – if specified period is found in the target worksheet.

[highlight txt=’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

 

[highlight txt=’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 parameters

[highlight txt=’Grow’] – 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.

[highlight txt=’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.

[highlight txt=’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.

 

[highlight txt=’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.

[highlight txt=’Left’] – This option adds column at the beginning of the data range after key column and enter the period identifier with data.

[highlight txt=’Right’] – This options add new column and copies the data at the end of the existing data range in the target worksheet.

 

[highlight txt=’Limits of period’] – This group of options allow you to limit the numbers of period in the target worksheet.

[highlight txt=’Unlimited’]- This option lets you copy unlimited numbers of column until you reach the limitation of selected Excel file.

[highlight txt=’Limit to’] – This option allows you to select the number of columns you wish to limit for historic data in the target worksheet.

 

[highlight txt=’Limitations‘] of [highlight txt=’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

Recommended Posts

Leave a Comment