Missing values from joined tables

[#1]

Hi

I have problem with two joined tables when creating a chart with a dimension from one table that is not in the second table.

I have two tables:

Forecast table:

Forecast Month	Sales Month	ProductID	UnitID	Value
Jan	Feb	A	1	10
Jan	Feb	A	2	20
Jan	Feb	A	3	10
Jan	Mar	A	4	20
Jan	Mar	A	5	10
Jan	Mar	A	6	20

Realised sales table:

Sales Month	ProductID	UnitID	REA Value
Feb	A	1	11
Feb	A	2	19
Feb	A	3	9
Mar	A	4	20
Mar	A	6	20
Mar	A	7	9

I would like to show the realised sales for each forecast month (ie. Jan, Feb, Mar), for a given ProductID (ie. A, B, C).

When loading the two tables, qlikview automatically joins the two tables by Sales Month, ProductID and UnitID. If I sum over the Sales Month 'Mar' for the Realised sales table, I correctly get 20+20+9=49. However, if I select the Forecast Month 'Jan' and the ProductID 'A' I only get the values where the joined tables are identical. That is, I am missing UnitID '7' and only summing for ID 4 and 6, 20+20.

Basically i have a table with the dimensions 'Forecast Month' and 'Sales Month' and the expression Sum([REA Value])

I have tried the following statements without any luck:

Sum({<[Forecast Month] +={$(=Null())}>}

Sum({<[Forecast Month] +={''}>}

Sum({<[Forecast Month] +={'-'}>}

Hope someone can help

Many thanks

Martin

[jonathandienst]

Martin

Try the following expression

Sum(Total <[Sales Month] [REA Value])

The set expressions will not work because the set filtering is performed before the chart is built and hos no knowledge of the dimension values.

If the above expression does not work, then it might be necessary to modify the data model. I suggest that you post a spreadsheet with some sample data.

Hope that helps

Jonathan

[#3]

Hi Martin,

I think you could solve it if you join the tables with a new field: the concatenation of Sales Month,ProductID and UnitID.

So, first load the Forecast table adding a new field like this:

Table:
LOAD
...
     Sales Month & '-' & ProductID & '-' & UnitID as %key
...

Then, load the second table (Realised Sales) by joining it with the previous table with the same combination of fields named %key too:

LEFT JOIN(Forecast Table)
LOAD     
     Sales Month & '-' & ProductID & '-' & UnitID as %key
     ReaValue
FROM <Realised Sales data source>;

I think that should work.

Kind Regards,

Borja

[#4]

Hi Martin,

tried to rebuild the app. Can't see where QV should be wrong, see attached exam app.

Hi Borja,

avoiding sync-tables isn't a bad idea, but it won't change anything in this case above.

Regards to all

Roland

[jonathandienst]

Martin

See attached sample

Regards

Jonathan

[#6]

Hi

Thank you all very much for your responses!

The 'Total' from Jonathan solved a part of the problem, but I have now struck a new problem. I only wish to sum the next four months of the forecast, and it has proved to be quite difficult to add restrictions to the Sales Month after imposing the Total clause.

I have tried the following:

=If([Sales Month]>=AddMonths([Forecast Month],1) and [Sales Month]<=AddMonths([Forecast Month],4),SUM(Total <[Sales Month]> [REA Value]), 0)

The result looks very good when using a table containing the 'Sales month' as a dimension, but as soon as this is removed from the dimension list (to resemble a chart), it breaks down.

I will try to make an excel file and a test QV with test data.

[#7 · Accepted solution]

Hi,

I solved the issue by adding a dummy table that handled the links. The table was created after loading all the data:

Forecast_Chart_Dates:

LOAD

          Distinct ForecastMonth As ForecastMonth_Chart,

          ForecastMonth ,

          SalesMonth

Resident ForecastTable;

Again thank you for the valuable input above.