Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: DOC-75 Started revising section 6

By the end of this chapter you will be able to:

  • Collect data from additional tabs in excel spreadsheets

A file containing a mapping of Counties to Regions can be found in the input files, at:

Table of Contents

In 2 Using Excel Templates, you downloaded train.zip and saved the files to <path>. Find this file: <path>\inputData\AccountTransactions\AccountTransactions.xlsx
On the . The second tab of this workbook – accountHistory – details for the accounts are provided. You will create a file collector to read in this information: accountHistory contains details of account transactions.  

Upload data from using a file collector

You have already learned how to upload data using a file collector in 3 Combining Data Sets. By default the file collector reads from the first sheet in the workbook. The stream that PhixFlow creates when you upload the data is configured to hold this data. For this exercise, you need the data from the second worksheet. You will need to set additional options to configure PhixFlow to use this sheet.

The easiest way to do this is to load the data from the first sheet, then edit the collector settings.

  1. Create a new model called Read Account History

...

By default the file collector is set up to read from the first sheet in the workbook – and the stream is configured to hold this data.
You will need to update this configuration to read from the second sheet:

...

  • Right click on the stream and select Permanently Delete

...

  1. .

    Expand
    titleHow?
    1. In the repository browser, right-click Analysis Models then click 
      Insert excerpt
      _add_object
      _add_object
      nopaneltrue
       Analysis Model.
    2. PhixFlow displays the empty model in the modelling pane on the left and opens the model settings tab on the right.
    3. In Basic Settings →  Name, enter Read Account History.


  2. Add a file collector and set Basic Settings → Name: LoadAccountHistory.

    Expand
    1. From the model toolbar, drag 
      Insert excerpt
      _add_file_collector
      _add_file_collector
      nopaneltrue
       into the model.
    2. PhixFlow displays the settings tab for the new file collector.


  3. In the settings tab toolbar, click 

    Insert excerpt
    _save
    _save
    nopaneltrue
    .

  4. Upload the data.

    Expand
    titleHow?
    1. In the model, hover your mouse pointer over the AccountHistory file collector to display the context toolbar and click 
      Insert excerpt
      _upload_file
      _upload_file
      nopaneltrue
      .
    2.  Navigate to the directory <path>\train\inputData\AccountTransactions\AccountTransactions.xlsx.
    3. Select the file AccountTransactions.xlsx and click Open, then click
      Insert excerpt
      _upload_button
      _upload_button
      nopaneltrue
      .


  5. PhixFlow adds a new stream to the model, called LoadAccountHistory.

Change the file collector to load the second worksheet


...

  1. In the model, right-click the new stream and click permenantly delete
  2. Double-click LoadAccountHistory to open its settings tab. Set Advanced → Excel Data Range Expression"accountHistory!A1:F100"

...

  1. In the File

...

  1. Coluns section

...

  1. , delete the column information that PhixFlow has already loaded.
  2. Click Image Added - Create  file attributes 
  3. PhixFlow already knows about the data in the spreadsheet. It now uses the expression you added to read the information about the columns in the second worksheet. PhixFlow adds the attributes to the list.
  4. In the model view, from the hover menu of the file collector press 

...

  1. Image Added - Create a new Stream

...

  1.  
  2. Save your changes
  3. Have a look the attributes in the new stream – you can see that these have been derived from the columns in the file collector

...

  1. Run analysis on the stream and confirm that the data that has been read into PhixFlow is from the second tab in the spreadsheet