Linking two tables some rows only

[#1]

I've been trying to find a solutions to the following:

I have a sales table as follows:

Item	ItemGroup	Buyer	Month	Week	Qty
1	21	A	201001	1	82
2	33	A	201001	1	58
3	2	B	201001	1	76
4	3	B	201001	1	59
1	21	A	201001	2	77
2	33	A	201001	2	29
3	2	B	201001	2	55
4	3	B	201001	2	60
…
1	21	A	201205	1	55
2	33	A	201205	1	11
3	2	B	201205	1	96
4	3	B	201205	1	66
1	21	A	201205	2	86
2	33	A	201205	2	75
3	2	B	201205	2	74
4	3	B	201205	2	82

and Forecast table as follows:

ItemGroup	Buyer	Month	ForecastQty
21	A	201201	91
33	A	201201	10
2	B	201201	27
3	B	201201	4
21	A	201202	74
33	A	201202	55
2	B	201202	52
3	B	201202	67
21	A	201203	10
33	A	201203	12
2	B	201203	45
3	B	201203	7
21	A	201301	24
33	A	201301	3
2	B	201301	4
3	B	201301	87

Note that the sales are for many years and forecast for current fiscal year. I wanted to build a chart with current sales and forecast qty for each month. In order to do this I created a key from ItemGroup, Buyer and 2 right most digits of Month. The same key was created in sales table.

My problem is since the same key exists mutiple times in the sales table when I use right(Month,2) as my dimension and sum(ForecastQty) as expression I get multiples of the forecast Qty. In order to solve this problem I used sum(DISTINCT ForecastQty) but I have a feeling this is not the best solution.

Is there a way I can limit a single row for key from sales to be associated with the forecast? Rather than having a many to one connection I would like to force one to one from sales to forecast. This way my sum would be accurate and I wouldn't have to use sum(DISTINCT ForecastQty)?

Thanks,

G

[Oleg_Troyansky]

G,

I think you should link by the full month number - you don't need to associate prior year sales to current year forecast, do you?

Even if you wanted to, you'd be better off creating a separate field for a Year and for the 2-digit Month number and using that as your dimension, while the link is still based on the full Year/Month.

The duplication of data is likely to happen if you try to show this calculation by Item. Since the forecast is only available by Item Group, if you present it by Item, the same Forecast Numbers will get counted multiple times.

Finally, - you are right, you definitely shouldn't use sum(distinct Forecast), this will simply skew the numbers, since many Item Groups can have a forecast of 1000, as an example.

[#3]

Oleg,

Associating the full year and month to the forecast is not adequet since I DO need to display past 2 years sales  along with current year forecast. This was the reason I ended up using the month.

Initially I used YYYYMM as the month value but as soon as I filter down to buyer I end up with the forecast number disapearnig since the sales for current year end in this month. Having MM as month value allowed me to display the forecast for full year even if the sales are zero for following months for this year.

Thanks,

Grif

[teempi]

Hi,

I think the easiest way is to just concatenate the tables together. Then you can filter rows by Item Group, Buyer and Month. Selecting Item or Week will make ForecastQty show nothing though (unless set analysis is used).

-Teemu

[#5]

Teemu,

Are you suggesting a dumb concatenations for example:

CONCATENATE (SALES_DATA) LOAD * RESIDENT FORECAST_DATA;

The aim was to preserve the drill down so that when user select an ItemGroup or even an Item the forecast will reflect the changes. I almost have to pick a single record from the data set and combine the forecast information to it.

I guess another method would be to create dummy item entries in Salesdata and use these dummy entries in forecast. Is there a way that I can select a single row for a given month for each of the 'ItemGroup_Buyer_Month" key from sales data?

Grif.

[teempi]

Hi,

Yes that's basically what I would probably do (at least on the concept level).

As Oleg already mentioned, the forecast data is only available by item group. This means that you can't really drill down to item level because the information doesn't exist (assuming forecasts are totals for that item group). There's no way for us to tell if item1 has 20% and item2 has 80% of the forecastqty etc.

-Teemu

[#7]

Hi Teemu,

I am not convinced that the dumb concat would be sufficent to allow drill down to the product category level for given month. I would be satisfied with the category breakdown since the forecast data is not granular to item level. It can only be drilled to the category level.

My biggest issue is I would like to have unique records or associate forecast data to unique records for each category, for each buyer for months 1 to 12. I am not interested in year at this point since the forecast will only be valid for current year.

I am not sure how I can extract few records from my record set to link the forecast. I was trying to use following example from http://community.qlik.com/message/18493

Itemtemp:

LOAD

[Part Number],

[Item Status],

[Commodity Code],

recno() as Recno

FROM $(Stage2QVDPath1)\ItemMaster.qvd(qvd);

Itemtemp2:

JOIN (Itemtemp)

LOAD DISTINCT

min(Recno) as Recno,

[Part Number],

[Item Status],

[Commodity Code]

RESIDENT Itemtemp

GROUP BY [Part Number],[Item Status],[Commodity Code]

ORDER BY [Part Number]ASC,[Item Status]ASC,[Commodity Code]ASC;

Grif.

[teempi]

Hi G,

I think I've misunderstood what you need. I've attached an example file that shows you what's possible with simple concatenation. I've modified the data so that there are values for this year only (Qty for months 1-5 and ForecastQty for 1-9). I hope this didnt change the scnerario compared to yours

If that wasn't what you need, could you post an example table that illustrates the end result you want.

-Teemu

[#9]

I think the only thing missing is the fact that for each month we would have data for multiple weeks.

Please see attached example.

Here is the gist of the form:

1. Display in one chart sales for 2011 and 2012 along with Forecast for fiscal year 2012.

2. When a buyer is selected display information for the buyer and have the ability to drill down to productID level.

Current issue is that I have to create a key based on buyer, productID and month (month is only 2 digit and not YYYYMM) format since I want to be able to display the forecast for complete year 2012 not just upto current month.

You'll notice that creating a key where month is MM rather than YYYYMM allows me to do that. If I create a key with MMYYYY then the forecast cannot be displayed past current month.

[teempi]

Hey,

I made a new version and used concatenation. Isn't this what you need? If not, please create an example table by hand to illustrate what you mean

-Teemu