13 Replies Latest reply: Jul 13, 2012 10:04 AM by Derek Jones RSS

    Unintended join on tables

    Derek Jones

      Hi All

       

      I'm hoping someone can help. I have an application with many tables which I've built my own relationship table to handle all the table joins.

       

      I've now got an issue where there is an unintended relationship between two tables which is giving me an incorrect result.

       

      I've attached a small simple test application that should describe the issue (I've also attached the Excel doc with the raw data if that helps on changing the load script).

       

      The issue is as follows..

       

      I have a table called 'XRef' which has a cross reference of Items to a customer item In the example attached, ItemID = 1 is currently referenced to customer item 'xa'. Now in the past, customer item 'xa' was referenced to ItemID = 2 and you'll see in the sales table it has a sale of item xa (XrefID = 1) to ItemID = 2, I do want to show this in sales s it was a sale of 'xa', so in my straight table the total sales quantity sold of 30 of Customer Item 'xa' is correct as I do want to view the entire history of sales of 'xa'.

       

      The stock information should only point to the stock of the ItemID which is currently mapped to item 'xa' in the XRef table, hence as it's currently mapped to ItemID = 1, stock is 0 so it should show 0 stock, however it's picking up the stock onItemID = 2 too which is not what I want.

       

      I've intentionally used a relationship table in order to force the joins to the data I require but there's still an unintentional join to ItemID = 2 via the Sales relationship.

       

      My goal is a table showing the following:

       

       

      CustomerItemItemNamesum(Qty)sum(Stock)
      xaItemA300

       

       

      I thought of using two seperate identical Item tables, however I use other fields in the Items table in my app as selection boxes e.g. Manufacturer, Barcode, Colour etc, if I have two item tables then a selection of Manufacturer from the original Item table will not filter by Manufcaturer in this example as it would be working off an independent Item table.

       

      Ideally I want it to work off the current Item Table. If anyone has any ideas of how I can make it look at all sales, but only item data from it's current cross reference to an item I would be most grateful.

       

      My thanks in advance

       

      Derek

        • Re: Unintended join on tables

          Hi Derek

          By taking a quick look at the excel file, I noticed that on the Sales tab you have this:

          SalesIDItemIDXrefIDQty
          11120
          22110

          On the second SalesID you have the same XrefID as SalesID 1, so when you join that with the Xref table, you have 2 values for Xa (20 for salesID 1 and 10 for SalesID 2)
          If you remove the XrefID 1, you from the incorrect one, you will get the desired results

          The other way to solve it, is if you only want one SalesID to be valid, on your expressions you should use set analysis saying <SalesID = 1>
          Hope this helps

           

          Regards,

            • Re: Unintended join on tables
              Derek Jones

              Thanks Pablo for responding.

               

              Unfortunately the Sales XrefID being reported against ItemID1 and ItemID2 is intentional as this is how the data behaves in my dataset. To explain, a customer calls what he orders 'xa', at one point in time he used to buy ItemB so in sales history we do want to record against 'xa' he's sold ItemB as this is a record of his demand on this item. However when it comes to stock, as he now buys ItemA as 'xa' and is no longer interested in ItemB. Example of this is where on a laptop there's a CPU upgrade in ItemA, so why would he ever want to purchase ItemB (with the lower CPU) again. So I now only want to show available stock of ItemA and that's why in the Xref table I only map ItemB to 'xa' so it's trying to force only showing stock on ItemB.

               

              Your set analysis is specific to that particular record, in my actyual application I have multiple customer items mapped to multiple items and with millions of sales records too. FYI I tried adding set analysis to the stock expression to only include records where an xRef record exists i.e.

               

              =

              sum({<Join_Xref={'>0'}>} Stock)

               

              But this didn't help

                • Re: Unintended join on tables

                  Hi Derek
                  I think I understand better now...
                  I only have one question to see if I got it...
                  Your goal table shows item A Sum(Qty) as 30 when in fact is 20. Do you want to sum everything for Qty and only >0 for stock? Your goal table shows 0 in Stock
                  How do you identify which one you want to show and which one you doesn't? How do I know that Item B is no longer valid?
                  I would suggest adding on your data model a flag that shows the active and inactive products and then you can use that on your set analysis.
                  Hope this helps

                    • Re: Unintended join on tables
                      Derek Jones

                      Hi Pablo

                       

                      Yes, Qty (which is coming from Sales) should be 30 as I want to include 'all' sales of Customer Item 'xy' no matter which internal item it came from. Stock however should only show stock from ItemA as this is the internal item that customer item 'xy' currently points to. In my example this is 0, however it could be 5 and I would want it to show stock at 5, what I don't want it to do is show the 10 in stock on ItemA.

                       

                      maybe it will help to look at it the other way around, I want Customer item 'xy' to be only joined to data for ItemB, i.e. If ItemA is coloured black and ItemB is coloured red (this colour is stored in the Item table), then if i select colour red, I would want 'xy' to show in the customer item list, but if I select colour black, it would not. However, the only exception is that I 'do' want it to sum all the sales for customer item 'xy' no matter what internal item it was assigned to.

                       

                      Hope this makes a little more sense.

                       

                      Thanks again for your interest.

                       

                      Derek

                        • Re: Unintended join on tables

                          I think I got it

                          Please take a look at the data and qvw sample to see if this is what you need

                          I only added a column on the xRef tab of the data file named Active and gave it a 1 value. You can do that for all the values on the data itself or in the load script

                          Now I was able to use set analysis and the Qty 30 Stock 0

                          Let me know if it worked

                            • Re: Unintended join on tables
                              Derek Jones

                              Hi Pablo

                               

                              Unfortunately this hasn't worked, if you now change the stock data so ItemID=1 has a stock of 5, then it should show Stock of 5, but it still shows 0?

                               

                              Also, my end goal is that I can pull item information through into this table too. I've amended the app you sent and the data file to show what I mean. Now I want to report the 'colour' of item 'xa' and as 'xa' is mapped to ItemID=1 then it's colour is 'red', butwhen i bring in colour it splits out to show 10 sold on colour 'Black'.

                               

                              Thanks again for trying.

                               

                              Derek

                                • Re: Unintended join on tables

                                  Dumb mistake on the set analysis formula syntax from my side, 0 was a coincidence

                                  Now I am almost sure I got what you want
                                  Please let me know

                                    • Re: Unintended join on tables
                                      Derek Jones

                                      Thanks Pablo, It gets around the issue of reporting the stock of ItemB, however I still have the issue around the customer item still joining to ItemB and reporting the colour of ItemB of Black aswell as Red shich is the colour item 'xa' is currently.

                                       

                                      I almost need the reverse where the only join to Items from Customer Items is to ItemA, but using set analysis I can force it to include any sales in the table so it includes sales from ItemB.

                                       

                                      Its almost like a slowly changing dimension where item 'xa' has changed over time, what I want to record on the item propoerties is the current stock and colour of the item, but historically on sales I want to include all items as it's the history of the demand for that item.

                                       

                                      Hope this make sense.

                        • Re: Unintended join on tables
                          Derek Jones

                          Hi All

                           

                          Maybe this will help, I've flipped the issue around now so Customer Item 'xa' only relates to ItemA, so stock and item against customer item 'xa' only points to anything on ItemA. However my goal is that CustomerItem 'xa' has an XrefID of 1, we record this XrefID in the sales table (as SalXrefID) and at one point in time, 'xa' was mapped to another ItemID. I want to show for CustomerItem 'xa; only the item properties and stock agianst it's current Item mapping (to ItemA), but sales for any item that a sale was recorded against the CustomerItem XrefID. Is this possible?

                           

                          My thanks in advance for any suggestions

                           

                          qvxref.JPG

                            • Re: Unintended join on tables

                              Hi Derek
                              In order to accomplish what you need, I will add which one is actve and which one is not so you can do
                              Sum(Qty) and it will get active and non active = 30
                              Sum({<Active={1}>}Stock) will get the current item stock = 5

                              Sum({<Active={0}>}Stock) if you want to see stock of old items = 0

                              I attached the new Data and qvw for reference

                                • Re: Unintended join on tables
                                  Derek Jones

                                  Thanks again Pablo for your help.

                                   

                                  It however still does not solve my issue around restricting the item dimensions to what 'xa' is currently assigned to e.g. 'xa' is currently colour of red as it's mapped to ItemA, so if I had a listbox with colour and I were to select 'red' I would want 'xa' to show with Sales of 30 and stock of 5 still. However this is not possible still as there is still this unintended join to items from sales, so sales will drop to 20 if I selected red which is not what I desire.

                                   

                                  Getting closer to my goal thankfully. I'm thinking maybe I need to create a secondary sales table with xref data only (so no join to Items), then I can use this to get out all sales on xrefID = 1, whilst still only joining to the Items table by it's current item mapping. My only concern is my actual real life sales table is the largest table in my app at around 4m rows, so duplicating this is my last resort.

                                   

                                  Thanks again

                                   

                                  Derek