Complex Spotfire Join

Question

I am attempting a complex join. I have tried a Left Outer, Left Single, Full, Inner, Right Outer and Right Single and none of them have produced the result I need. I feel it might be easier to explain with Pictures so here it goes. In Pic 1 you can see my main Data Table Total Sales_2.

What I want to do is bring in the Value column from ROI RUBU Schema see Pic 2 for the corresponding Product on the corresponding Schema into Total Sales_2

So in Pic 1 you can see I have a line highlighted in green. It has its account number TY00009, its Schema Name ROI_RUBU_SCHEMA_8, the product number 156118, the product name, Estrumate 1x20ml 195, the Qty 10 and the Total Value 424.50 what I would like to see in the next column in Total Sales_2 is Value and 0.20 but I don't get that see Pic 3

I have used and a Left Outer join in this example placed on the Product code to bring in the Value Column from ROI RUBU Schema see Pic 2 into Total Sales_2

I was expecting to see 0.20 as I have a line highlighted in green in Pic 2 because ROI_RUBU_SCHEMA_8 gives back 0.20 for product 156118

As you can see in Pic 3 I get different values back for Product 156118 on ROI_RUBU_SCHEMA_8 See Pic 3

What I need is the value to be 0.20 for all on Product 156118 on the account TY00009 because its on ROI_RUBU_SCHEMA_8

If TY00009 was on ROI_RUBU_SCHEMA_9 I would expect to see 0.27 for Product 156118 as that is what ROI_RUBU_SCHEMA_9 gives back on Product 156118 again see Pic 2 ROI RUBU Schema

I have tried every join there is available and no luck. If anyone can offer any help it would be greatly appreciated.

Pic 1

Pic 2

Pic 3

So based on feedback from an administrator please find what I believe to be a useful amount of data that can be used to recreate what I am currently facing. If more data is needed I am happy to supply

Total Sales_2 Data

Customer Maxx name  Customer Maxx Code  Schema Name Product Code    Product Description Sold Quantity   Total Value
Random Customer Name    TY00009 ROI_RUBU_SCHEMA_8   156118  Estrumate 1x20ml 195    10  424.5
Random Customer Name    TY00009 ROI_RUBU_SCHEMA_8   119977  Rotavec Corona 1x40ml 125   30  5222
Random Customer Name    TY00009 ROI_RUBU_SCHEMA_8   30179   Bovilis BVD 1x25ds 195  3   424.5
Random Customer Name    TY00009 ROI_RUBU_SCHEMA_8   21406   Delvosteron 1x20ml 125  6   5222
Random Customer Name    TY00009 ROI_RUBU_SCHEMA_8   13262   Dexafort 1x50ml 195 4   5222
Random Customer Name    TY00009 ROI_RUBU_SCHEMA_8   156118  Estrumate 1x20ml 195    12  4444
Random Customer Name    TY00009 ROI_RUBU_SCHEMA_8   119977  Rotavec Corona 1x40ml 125   32  3333

ROI RUBU Schema Data

Product Number  Product Name    List Price Oct 1st 2018 Category    Value
156118  Estrumate 1x20ml 100    €28.50  ROI_RUBU_SCHEMA_8   0.2
156118  Estrumate 1x20ml 100    €28.50  ROI_RUBU_SCHEMA_9   0.27
156118  Estrumate 1x20ml 100    €28.50  ROI_RUBU_SCHEMA_10  0.16

Answer 1

So I finally got there, and I wanted to give the answer so that it may help others.

It was simple really and silly on my part. It was a left outer join that was needed, but I only bought in 1 Column Value from the ROI RUBU Schema table, so when I saw all the different values for ROI_RUBU_SCHEMA_8 on data table Total Sales_2 Data i thought the join was wrong. If I had of also bought in the Category Column from ROI RUBU Schema table into Total Sales_2 I would of seen all the possible Schema. I simply did not bring in Category Column from ROI RUBU Schema table. Silly but it happened :-)