9 Replies Latest reply on Oct 23, 2016 11:51 AM by nataraj.vijayanagaram

    Data Mapping with multiple lookup columns

    nataraj.vijayanagaram

      Hi,

       

      I am working on a dashboard with two tables and stuck with an issues, Looking for some expert advise:

       

      Have two tables as below:

       

      Table 1:

      CityCountry
      HyderabadIndia
      MumbaiIndia
      New YorkUSA
      LewisvilleUSA

       

      Table 2:

       

      InvoicePrimary SaleItems
      Secondary SaleItemsTertiary SaleItemsQuaternary SaleItemsTotal Items
      ABX1234Hyderabad5Bengaluru6Mumbai8Lewisville524
      ABZ1233Lewisville10Hyderabad8New York2Bengaluru727
      AYB1243New York7Mumbai3Hyderabad6Bengaluru420

       

      Using the above tables, I would like to do the following:

       

      1. Create a Parameter and use it as a filter based on the Table 1, however this needs to be a filter across the dashboard

      2. I would like to show the items per country and city based on the filter Selection. Example - If the user Selects Hyderabad, then I would like to show the total for Hyderabad as 19. Can you please let me know if there is a way to do a setup to achieve this.

       

      Unfortunately it is not possible to change the structure of the second table as it has few underlying statements / workflows. Please advise.

       

      Thank you!

        • 1. Re: Data Mapping with multiple lookup columns
          Keshia Rose

          Hi Nataraj,

           

          What kind of data source are you using? Could you use custom sql to reshape the data? I pasted your tables into an excel file as two separate sheets and was able to create the following custom sql that might get you what you want:

           

          SELECT [Sheet2$].[Invoice] AS [Invoice],

            [Sheet2$].[Primary Items] AS [Items],

            [Sheet2$].[Primary Sale] AS [Sale],

            "Primary" AS [Type]

          FROM [Sheet2$]

           

          UNION ALL

           

          SELECT [Sheet2$].[Invoice] AS [Invoice],

            [Sheet2$].[Secondary Items] AS [Items],

            [Sheet2$].[Secondary Sale] AS [Sale],

            "Secondary" AS [Type]

          FROM [Sheet2$]

           

          UNION ALL

           

          SELECT [Sheet2$].[Invoice] AS [Invoice],

            [Sheet2$].[Tertiary Items] AS [Items],

            [Sheet2$].[Tertiary Sales] AS [Sales],

            "Tertiary" AS [Type]

          FROM [Sheet2$]

           

          UNION ALL

           

          SELECT [Sheet2$].[Invoice] AS [Invoice],

            [Sheet2$].[Quaternary Items] AS [Items],

            [Sheet2$].[Quaternary Sales] AS [Sales],

            "Quarternary" AS [Type]

          FROM [Sheet2$]

           

          Note: This SQL works is written for excel but could most likely be used in your data source

           

          Then I joined in your first table make the following final table:

           

          From there, doing your analysis is simple since all cities are in one column, you can just create a city filter:

           

          Of course this is just one way to go about this and may not be the best or most elegant depending on your environment but just thought I would offer an idea.

           

          Take care,

          Keshia

          • 2. Re: Data Mapping with multiple lookup columns
            nataraj.vijayanagaram

            Hi Keshia,

             

            Apologies for not providing the information regarding data source. My data tables are on SharePoint 2010 and I am using OData Connection to connect to them. I am not sure if we can use a custom SQL code to reshape the data. Please advise if it can be done.

             

            Thank you!

            Nataraj

            • 3. Re: Data Mapping with multiple lookup columns
              Keshia Rose

              Hmmm, well that definitely makes it more tricky! Here's another idea then, not as nice as the first. You could connect to the data table four different times. Once for Primary, Secondary etc. For each one you would hide all but one type of sale. For example, Tertiary would only keep Invoice, Tertiary Sales and Tertiary Items. Then you can blend them together by setting the blend relationship to be city = primary sales, city = secondary sales etc. From there you can just add them up to get the sums for all cities:

              ZN( SUM( [Primary].[Primary Items] ) )

              +ZN( SUM( [Quaternary].[Quaternary Items] ) )

              +ZN( SUM( [Secondary].[Secondary Items] ) )

              +ZN( SUM( [Tertiary].[Tertiary Items] ) )

               

              I only tried this in excel so I can't guarantee that it will work with SharePoint, but I don't see any reason why it wouldn't.

               

              Let me know if it works out!

               

              Take care,

              Keshia

              1 of 1 people found this helpful
              • 4. Re: Data Mapping with multiple lookup columns
                nataraj.vijayanagaram

                Hi Keshia,

                 

                This worked perfectly. Thank you very much for your solution, This is a great help.

                 

                Thank you!

                Nataraj

                • 5. Re: Data Mapping with multiple lookup columns
                  Keshia Rose

                  Glad to hear it worked out Nataraj! Please mark that response as correct so that others know this has been addressed.

                  • 6. Re: Data Mapping with multiple lookup columns
                    nataraj.vijayanagaram

                    Hi Keshia,

                     

                    I am not able to see the option to mark this as correct. Can you please advise.

                     

                    Thank you

                    • 7. Re: Data Mapping with multiple lookup columns
                      nataraj.vijayanagaram

                      Hi Keshia,

                       

                      Also, I am able to get the data into the single table with the solution you have provided however when I am trying to pull this info onto the chart it shows as NULL with AGG(Total) however When I am trying few options I was able to pull it as SUM (Not sure how ) which actually gave me the values.

                       

                      Is it possible to change the AGG to SUM in the Total field in the marks section. Please advise.

                       

                      Screen Shot 2016-10-15 at 5.36.52 PM.png

                       

                      Thank you!

                      Nataraj

                      • 8. Re: Data Mapping with multiple lookup columns
                        Keshia Rose

                        Hi Nataraj,

                         

                        You are seeing the AGG because you have already done the SUM in the calculation. Could you show me what your calculation for total looks like? Also, make sure that you are blending correctly, the numbers may not be correct if you haven't activated the blend. See here (specifically step 4) for more information on blending.


                        Take care,Keshia

                        • 9. Re: Data Mapping with multiple lookup columns
                          nataraj.vijayanagaram

                          Hi Keshia,

                           

                          As suggested, it was an issue with data blending. I was able to get this fixed.

                           

                          Thank you!

                          Nataraj