5 Replies Latest reply on Nov 9, 2016 3:58 PM by Daniel Schwan

    How to get blend to act as join?

    Daniel Schwan



      I trying to to use an excel spreadsheet as a lookup table for an SQL data source. I think my only option is to use a data blend. But, then I can't get the aggregation that I want. Please help. Details below.


      The attached twbx recreates my issue.


      I have a SQL data source (ORDERS) which gives me a list of orders delivered by delivery date, along with order properties. I have a separate excel spreadsheet (PRICES) which gives the value of products. I can create a calculated field in ORDERS to get a product of each order. I really want a join here to get a value for each order, but since product is a calculated field of ORDERS, I cannot join to PRICES, I have to do a blend. That's no problem, but when I blend, the data sources are pre-aggrated and I can't get a value for each order without table calcs.


      In this toy example, I can create a calculated field in ORDERS which actually gives me the value for each order: Calculated Revenue. I'm trying to create a calculated field which uses the Price field in PRICES to match Calculated Revenue. I can do it for a simple table, but the calc returns the wrong value when I change the aggregation. Dashboard 1 shows the difference between Calculated Revenue and SUM(Prices) for daily revenue.


      I'm know I could potentially use custom SQL to calculate Product within ORDERS then do a cross-data join, but am hoping to avoid that. Anyone have a different solution? I'm looking for a different way to join/blend the data or either for a calculation that produces the value.