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

• Trouble shoot "Output attribute *** has evaluated to a set of 2 values rather than a single value..."
• Trouble shoot Lookups
• Trouble shoot Database Collectors
• Trouble shoot models with debug()
• Trouble shoot large data sets with stop()

Trouble shooting "Output attribute *** has evaluated to a set of 2 values rather than a single value..."

• Drag Streams
  • MC Troubleshoot Account List
  • MC Troubleshoot Phone Numbers

on to your model

• Create a merge using only MC Troubleshoot Account List as an input
  • Use the field ACCOUNT_NUM as the merge key
• Link MC Troubleshoot Phone Numbers to your merge with a pipe, and manually configure the grouping attribute ACCOUNT_NUM on the pipe
• Drag the attribute PHONE_NUMBER onto the merge

When you run analysis on Accounts with phone numbers you will get an error message like: "Output attribute "PHONE_NUMBER" has evaluated to a set of 2 values rather than a single value while processing key value ..."
Look at the data in MC Troubleshoot Phone Numbers. You will see that for the key value given in the error message, there are two records – two different phone numbers for that customer.
This type of error message is common when manually setting up merges and aggregates in PhixFlow. Look at the expressions for the attributes in the merge. Those created with the automated merge builder use an expression getElement(…) on each input to avoid this problem.
You can often use just this approach - getElement(…)- to return the first record for the key value. However, in this case you want to show all phone numbers returned:

• Use the function listToString in the expression for PHONE_NUMBER, in the Stream Accounts with phone numbers, to print out all phone numbers for each account as a comma-separated list

Trouble shooting Lookups

• Drag the Stream MC Troubleshoot Post Codes on to your model
• Add a lookup pipe from this Stream to Accounts with phone numbers
• Add a match on the lookup pipe: ACCOUNT_NUM = ACCOUNT_NUM
• Drag the attribute POST_CODE from MC Troubleshoot Post Codes into Accounts with phone numbers
• Drag this attribute in Accounts with phone numbers to the top of the attributes list
• Run analysis on Accounts with phone numbers
• You will see that the values in the attribute POST_CODE are all blank

This is because PhixFlow processes attributes in order. When, in the lookup pipe, you refer to _out.ACCOUNT_NUM you are using the value of the attribute ACCOUNT_NUM in the output of the Stream Accounts with phone numbers. If this is not available at the time you use it, the lookup will return no values.

• Drag the attribute POST_CODE to a position in the attributes list underneath the attribute ACCOUNT_NUM
• Run the stream
• You will see that this time the values in POST_CODE have been populated

Trouble shooting Database Collectors

• Add a database collector to your model to read data from the table SOURCE_MC_TROUBLE_CUST_SIGNUPS, using Datasource CRM, and using the query:

select * from SOURCE_MC_TROUBLE_CUST_SIGNUPS
where COLLECTION_DATE > to_date({_fromDate}, 'yyyymmdd.hh24miss')
and COLLECTION_DATE <= to_date({_toDate}, 'yyyymmdd.hh24miss')

• Create a stream from this collector
• Set the Start Date of the Stream to 23/03/2009
• Try running the Stream; you will see that no data is collected.
• To see the query that was run, open the Console and press - System Logging Configuration
• Tick the option Log Collector Statements
• Rollback the Stream, unticking the flag Keep Old Stream Sets
• Find the log file for this analysis run - you will see a log message for the data collection, giving the query that was run
• From this you can see that the query run will return no data from the table
• Rollback the Stream, unticking the flag Keep Old Stream Sets
• Update the Start Date of the Stream to 20/03/2009.
• Re-run the Stream
• You will see that this time data was collected from the table
• Check in the log file for the query that was used on this run

Trouble shooting Models with debug()

• Drag Streams MC Trouble Billed Totals and MC Trouble Customer List on to your model
• Create a new Stream Custs With Billed Totals that copies data from MC Trouble Customer List
• Add a lookup pipe from MC Trouble Billed Totals into Custs With Billed Totals to read in, for each customer, the billed total

You are expecting that a billed total will be found for every account

• Run Analysis on Custs With Billed Totals – you will see that in some cases this is not true

To help with further development of this model you decide that it would be useful to see, in the log file, a record of every case where a billed total could not be found:

• Add a debug() statement to your Stream to print a log message whenever a billed total cannot be found for a customer

Trouble shooting large data sets with stop()

• Create a new Stream Custs With Billed Totals 2, which, like Custs With Billed Totals, reads its input from MC Trouble Customer List and looks up, for each customer, the billed total from MC Trouble Billed Totals

In this case, you decide that as soon as you have found a single case where the billed total cannot be found for a customer there is no point in continuing the analysis:

• Add a stop() statement to your Stream to print a log message when a billed total cannot be found for a customer, and stop processing

Note: you may in fact see more than 1 message from stop() – this is because PhixFlow processes data in parallel threads, and so several data records may be processed before the stop() is enforced. However, when processing large data sets stop() can be useful since PhixFlow will stop the analysis run as soon as possible.