6 Replies Latest reply: May 10, 2012 4:05 AM by Martin Edwards RSS

    Missing values from joined tables



      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 MonthSales MonthProductIDUnitIDValue


      Realised sales table:

      Sales MonthProductIDUnitIDREA Value


      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


        • Missing values from joined tables
          Jonathan Dienst



          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


          • Re: Missing values from joined tables

            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:



                 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)
                 Sales Month & '-' & ProductID & '-' & UnitID as %key
            FROM <Realised Sales data source>;


            I think that should work.


            Kind Regards,



            • Re: Missing values from joined tables

              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


              • Missing values from joined tables



                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.

                  • Missing values from joined tables



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




                              Distinct ForecastMonth As ForecastMonth_Chart,

                              ForecastMonth ,


                    Resident ForecastTable;


                    Again thank you for the valuable input above.