0 Replies Latest reply on Sep 27, 2018 2:41 AM by paolo.raia

    Lookup values from a secondary data source (Salesforce data)

    paolo.raia

      Hi,

       

      Difficult to explain this, so I hope I make sense!  I am using test data here and cannot attach a workbook just yet, but am wondering if there is some methodology that can give me the result I need.

       

      In my primary data source, I have three fields: Code / Detail / Result:-

       

      Code.....Detail.....Result

      A............A1.........Multiple

      A............A2.........Multiple

      A............A3.........Multiple

      B............B1.........B1

      C............C1.........C1

      D............D1.........Multiple

      D............D2.........Multiple

       

      Code and Detail are dimensions. Result is a calculation which returns "Multiple" if there are different Detail values for each Code, and just the Detail value if single.

       

      Now if the result is "Multiple", I then need to lookup the table values stored in the secondary data source (below) and return those values relating to the Code:-

       

      Code......Value

      A.............AA1

      A.............AA2

      B.............BB1

      B.............BB2

      C.............CC1

      D.............DD1

      D.............DD2

      D.............DD3

      D.............DD4

       

      So the final result would read:-

       

      Code.....Result

      A............AA1 (because this Code was a multiple, the result will return the values in the secondary)

      A............AA2 (because this Code was a multiple, the result will return the values in the secondary)

      B............B1 (returns the original value in the primary data source as this was not a multiple)

      C............C1 (returns the original value in the primary data source as this was not a multiple)

      D............DD1 (because this Code was a multiple, the result will return the values in the secondary)

      D............DD2 (because this Code was a multiple, the result will return the values in the secondary)

      D............DD3 (because this Code was a multiple, the result will return the values in the secondary)

      D............DD4 (because this Code was a multiple, the result will return the values in the secondary)

       

      Confused???

       

      I am also working with Salesforce data which is a challenge in itself.

       

      I have been spinning this around in my head, but cannot seem to find a logical solution to this.  Maybe it's staring me in the face and it's more simple than it sounds!

       

      Thanks in advance.