Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

  • 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  - Create a new Stream using the attributes returned from the query

...

  • Hover over LastBillAmount and press  - Add a new Connector
  • 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 
    • 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 Image Modified 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