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

    Missing values from joined tables

      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 MonthSales MonthProductIDUnitIDValue
      JanFebA110
      JanFebA220
      JanFebA310
      JanMarA420
      JanMarA510
      JanMarA620

       

      Realised sales table:

      Sales MonthProductIDUnitIDREA Value
      FebA111
      FebA219
      FebA39
      MarA420
      MarA620
      MarA79

       

      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

        • Missing values from joined tables
          Jonathan Dienst

          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

          • Re: Missing values from joined tables
            Borja Rodriguez

            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

            • 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

              Roland

              • Missing values from joined tables

                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.

                  • Missing values from joined tables

                    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.