...
- In the tab Query String enter:
| Code Block |
|---|
select |
...
CustomerRef, BillAmount |
...
from |
...
SOURCE_CUST_BILLS SCB1 |
...
where BillDate = (select max(BillDate) from SOURCE_CUST_BILLS SCB2 |
...
where SCB2.CustomerRef = SCB1.CustomerRef) |
- Go to the Details tab
- Select the Datasource CRM
- Tick the flag Enabled
- Tick the flag Allow Non-Scheduled Collection
- Press Apply
- Return to the Query String tab, and press
- Run query and see result - In a window that pops up you can see the data that will be returned by the query; this feature is a useful way to check that your query works
- Close the data window
- Press the button button
- Create a new Stream using the attributes returned from the query
...
- Hover over LastBillAmount and press press
- Move the pointer over the top of Customer Details with Region and select – this will attach the end of the pipe to the selected Stream
- In the Pipe Setup form that pops up:
- Enter the Name: lba
- Type: Look-up
- Data To Read: leave as the default – Latest
- Press Press
- In the matching attributes form that pops up create a match:
- Drag the attribute CUSTOMERREF from the left hand box into the list of matching attributes
- Drag the attribute CustomerRef from the right hand box into the list of matching attributes – on top of the entry for CustomerRef you created above
- This means that the lookup will match records in the reference data (LastBillAmount) where CUSTOMERREF = CustomerRef in the output data (Customer Details with Region)
- Press
on the pipe configuration form
- A lookup pipe will appear linking LastBillAmount to Customer Details with Region
- Drag the Attribute BILLAMOUNT from the Stream LastBillAmount onto Customer Details with Region
- Run Analysis on Customer Details with Region
- Check in the data in Customer Details with Region that the last billed amount has been retrieved for all records