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

So far your model has merged data from two files, then grouped and sorted the records. You now have a table that has the latest data about the TV Package that each Customer has:.

Image Removed

You now want all the channels that the customer has ordered. You will look up the data about the channels provided by each package from a database table. To make sure that PhixFlow finds all the channels, you will use an output multiplier.

Load data from a database table

First, load the database table SOURCE_PACKAGE_CHANNELS into a table as described below. Your model will then have the list of channels available in each package:

1. In the repository, expand the list of Datasources.
2. Drag the datasource called Rating and Billing into your model.
   1. In the Properties (on the right), in the Datasource Instances section, double-click Default
   2. Update the fields URL, Username and Password with those provided in your training email.
   3. Save your changes.
3. In your model, hover your mouse pointer over Rating and Billing and click 

   Insert excerpt
   _datasource_tableshow_databasetables _tabledatasource_show_databasetables nopanel true

   . PhixFlow displays a list of the tables available in this database. You can resize the list to make it easier to browse.
4. Right-click on SOURCE_PACKAGE_CHANNELS and select 

   Insert excerpt
   _collector_new _collector_new nopanel true

   . PhixFlow adds a new

   Insert excerpt
   _database_collector _database_collector nopanel true

    to the model, and gives it the same name as the source data table, SOURCE_PACKAGE_CHANNELS. In the property tab the Properties (on the right) → Basic Settings section → Statement Expression, you can see the database query is: 
     select * from SOURCE_PACKAGE_CHANNELS.
5. Hover your mouse pointer over the new new SOURCE_PACKAGE_CHANNELS database collector, and click 

   Insert excerpt
   _table_with_attributes _table_with_attributes nopanel true

   . PhixFlow adds a new table to your model and also gives it the name SOURCE_PACKAGE_CHANNELS. In the property tab Properties → Attributes section, you can see that the data has two attributes (columns): Package and Channel.

6. To load the data into the table, run analysis on this section of the model and check the data has loaded.

   Expand
   title How?
   Hover your mouse pointer over the SOURCE_PACKAGE_CHANNELS table, click  Insert excerpt _run_analysis _run_analysis nopanel true  then click YES to confirm. When PhixFlow notifies you that the analysis is finished, hover your mouse pointer over the table again. Click  Insert excerpt _view_show _view_show nopanel true . In the drop-down list, select  Insert excerpt _view_default _view_default nopanel true .  PhixFlow displays the data.

   Screenshot of your model and the data you have loaded:

   
   

7. 

Take a moment to look at this data. For each package, there are multiple channels included. For example, the News package includes Channel 1, Channel 2, Science 2 and NewsExtra.

Add another table 

Update your model to add a table. This table will take all the data that we have merged, sorted and grouped in the model so far:

1. Hover your mouse pointer over the Latest Package Update table and click 

   Insert excerpt
   _table_new _table_new nopanel true

   .
2. In Basic Settings, set the Name to Latest Package All Channels.

3. Drag all the attributes from 

   Insert excerpt
   _attributes _attributes nopanel true

    from Latest Package Update to the property tab for Latest Package All Channels.

   Expand
   title How?
   Hover your mouse pointer over the table Latest Package Update. In the context toolbar, click  Insert excerpt _attributes _attributes nopanel true . Phixflow lists the data attributes (column titles) for this table. Select all the attributes and drag them into the Latest Package All Channels  property tab → Attributes section.

   
   

4. Click 

   Insert excerpt
   _finish _finish nopanel true

    to save and close the property tabProperties.
5. Close the attributes list.

Supplement the table with look-up data

You now need to add to the data in the Latest Package All Channels table. You will use a look-up pipe to add the channels provided by a package. PhixFlow needs to know how the data in the source table matches the data in the receiving table.

1. In the model, add a pipe from SOURCE_PACKAGE_CHANNELS to Latest Package All Channels. PhixFlow opens the property tab the Properties for the new pipe.
2. In the Basic Settings section, set:
   • Name: find_channels
   • Type: Look-up
3. Click 

   Insert excerpt
   _save _save nopanel true

    to register the change of pipe.
4. In the Order/Index section, click 

   Insert excerpt
   _attributes _attributes nopanel true

   . PhixFlow opens two lists of attributes: one list for the source table and another for the receiving table. 
   1. From the source table list, on the left, drag PACKAGE into the Order/Index grid.
      
   2. This tells PhixFlow about the key column in the source table. Notice that PhixFlow has added a line to the Order/Index grid.
5. From the receiving table list, on the right, drag Package over the top of the line in the Order/Index grid.
   
   1. 
   
   This tells PhixFlow about the matching key column in the receiving table. Notice that PhixFlow has now added the Index Expression _out.Package. However, we do not want PhixFlow to use _out.Package because output multipliers are calculated before the output values is generated (see PhixFlow Timing Cycle). We need it to use the data where PACKAGE = Package is from the input pipe in.
We must do this, rather than using the value of Package from the output, because we are going to use the lookup to drive an output multiplier – and output multipliers are calculated before the output is generated.
   
6. In the Order/Index section, double-click the new line that PhixFlow has added. PhixFlow opens the property tab for the PACKAGE attribute. Set:
   •  In the Basic Settings → Index Expression change from _out.Package to in.Package.
   • Click 

     Insert excerpt
     _finish _finish nopanel true

      to save and close the property tab.
7. Close the attribute list.
8. In the pipe property tab, click 

   Insert excerpt
   _finish _finish nopanel true

    to save and close.
9. In the model toolbar, click 

   Insert excerpt
   _save_saveModel _save_saveModel nopanel true

    to save your model, which should look like this:
   

At this point, if you run your model it will find each customer, e.g. C010, and will read that the customer has the News package. It will then look-up the channel for this to find News: Channel 1. But having done one look-up for the customer it will move onto the next customer. 

Set an output multiplier

An output multiplier tells PhixFlow to process the same record multiple times, each time producing a new record. This is called breaking-out a record. In this case we want PhixFlow to keep processing the same customer record until it has found all the channels. This means our single input record:

becomes multiple output records, like this:

The output multiplier needs to know:

• which pipe it will use -  find_channels.
• what data you want to add - the channels.

The table also needs to create the new column for the channel information.

1. In the model, double-click Latest Package All Channels to open its property tabProperties.
2. To create the output multiplier, expand the Analysis Options section and set:
   • Output Multiplier: find_channels.CHANNEL

3. To create a new column for the channel data, expand the Attributes section. Click 

   Insert excerpt
   _new _new nopanel true

    to add a new attribute. PhixFlow opens a new attribute property tab. Set:
   •  Name: Channel
   •  Expression: _outputMultiplier
4. Click 

   Insert excerpt
   _finish _finish nopanel true

    to save and close the property tab.In the model toolbar, click  Insert excerpt_save_savenopaneltrue to save your model on both the New Table Attribute Properties and the Latest Package All Channels Properties.
5. Run the model and view the table data.

There should be 33 records

Image Removed

, similar to the below image:

Image Added

Notice that there are now multiple records for each customer. In the model toolbar, click