Unintended join on tables

[derekjones]

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:

CustomerItem	ItemName	sum(Qty)	sum(Stock)
xa	ItemA	30	0

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

[#2]

Hi Derek

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

SalesID	ItemID	XrefID	Qty
1	1	1	20
2	2	1	10

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,

[derekjones]

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

[#4]

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

[derekjones]

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

[#6]

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

[derekjones]

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

[#8]

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

[derekjones]

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.

[derekjones]

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