Export / Import objects into a different instance

Create some new objects in your configuration (this is the best approach – otherwise it may be confusing trying to distinguish your objects from objects of the same name on a different PhixFlow instance). Export some objects from your PhixFlow instance, and import them into another PhixFlow instance – in fact, you could even import them back into your instance, but try importing these into the PhixFlow instance of one of the other delegates.
Or – if you want, create a new instance of PhixFlow on your machine, and import the objects into that. Your PhixFlow trainer can help you do this, but you should start by trying to follow to the guidelines in the document PhixFlow Installation Guide under the section User Documentation in the PhixFlow help.
Make some changes to these objects.
Export them, and then import back into your instance.
Verify that the model has been properly imported. Note that collectors and exporters are set to Not Enabled, and that (for security reasons) the export does not include data source instance information (that is, database connection details).

Techniques for creating test data

It is often useful to create some simple test data in a Stream when creating a new model. A useful technique for this is:

• Create a new Stream
• Enter the expression 1..10 into the Input Multiplier
• Add some attributes to the Stream – some of them using the value _inputMultiplier in their expressions
• Run the Stream

You will see that 10 records are created.
Extend this technique and use the random() function to generate some test data.

Use output filter instead of mandatory pipe

Update the model you built in exercise 6 to use an output filter to achieve the same thing as you did with a mandatory pipe in the original exercise.

Use input multiplier to pass list to query on external database

Re-work the example in exercise Error! Reference source not found. to pass the list of accounts to the database collector as a list, rather than a series of individual account numbers. This is a more efficient configuration, since the query will then only be run once (in fact, the entire Stream calculation will only be run once since the output of the input multiplier will now be a list of only one item – that item itself being a list).
Hint: you construct a list in PhixFlow with the [ … ] notation. If you are hard-coding a list this will look like:
["red", "green", "black"]
but if you are passed a thing that is already a list – as in this case, when the notation acc.ACCOUNT_NUM is a list of account numbers provided by the pipe acc, then using the notation
[acc.ACCOUNT_NUM]
creates a list of one item – the item itself being a list.

Read excel spreadsheet with sub-headings

Find the spreadsheet C:\PhixFlow\train\inputData\AccountTransactions\AccountTransactions.xlsx. Open the spreadsheet and review the data. You will see that some lines, at the start of each section, have the details for the account – and all following lines omit these details, until the section for the next account starts.
Create a Stream to read in this data and populate, along with the transactions details, the account details for each record.
To do this:

• Use a Calculate by Set Stream – this ensures that the Stream processes the records in the same order that they are read in from the file
• Tick the flag Allow Partial Set Processing
• Use the cache() function to record the sub-headings as you find them
• When you test the value from a cell in an Excel spreadsheet, to see if the cell is empty, for a text the value will be an "empty" String rather than a null value. I.e. to see if the value from the Account Number cell is blank use

AccountNumber == ""
rather than
AccountNum == _NULL

Re-work cache extraction filter as attribute expression

Re-work the VAT rate lookup you did in exercise 14, using a Cache Extraction Filter – using an attribute expression instead.

• Keep the Order/Index Attribute – this will find all VAT rates for the TRANS_DESCRIPTION of the transaction being processed.
• Remove the Cache Extraction Filter, and use an attribute expression instead to find the VAT rate, from those supplied by the pipe for that TRANS_DESCRIPTION, that applies at the date of the transaction.

Apply VAT Rates with no end date

Drag AM Trans Amounts and AM VAT Rates 2 onto a model. You will see that the VAT rates in AM VAT Rates 2 have no end date. So to find the rate that applies for a transaction, you need to find the rate with the latest START_DATE on or before the transaction date.
Create a Stream to find the VAT rate for each transaction and apply it to the transaction amount.
Hint:

• In addition to the Order/Index Attribute on TRANS_DESCRIPTION, add an Order/Index Attribute START_DATE desc (with no Index Expression).
• Add a Cache Extraction Filter to retrieve only records where the START_DATE of the VAT rate is on or before the transaction date. This means that the pipe will return the VAT rates where:

1. The TRANS_DESCRIPTION matches that of the transaction being processed
2. The START_DATE is on or before the transaction date
3. The results are sorted by START_DATE desc

This means that you can pick the first record returned by the pipe to find the applicable VAT rate.