2 Replies Latest reply on Jul 5, 2016 10:46 AM by Ivan Young

    Compare two data sets from Excel and display details of the difference between two sets...

    Parag Sohoni

      Hi,

       

      I have two data sets in Excel

       

      Excel 1

      ------------

      Emp Id         Emp Name     Date of birth          Department

      1                    A                    1/1/1999               AA

      2                    B                    1/2/1999               BB

      3                    C                    1/3/1999               AA

      4                    D                    1/4/1999               AA

      5                    E                    1/5/1999               BB

       

      Excel 2

      ------------------

       

      Emp Id         Emp Name     Date of birth          Department

      1                    A                    1/1/1999               AA

      2                    B                    1/2/1999               BB

      5                    E                    1/5/1999               BB

       

      I can do the row count of the above and show case that in the grid as follows:

       

      Department        Excel 1          Excel 2          Difference (Excel 1 - Excel 2)

      ---------------------------------------------------------------------------

      AA                         3                    1                        2                          -- This basically says there are 2 rows for AA in Excel 1 more than Excel 2

      BB                         2                    2                        0                          -- This basically says there is no difference

       

       

       

      Now when I click the Difference 2 above for AA, it should display the records from Excel 1 which are not in Excel B as follows

       

      Emp Id         Emp Name     Date of birth          Department

       

      3                    C                    1/3/1999               AA

      4                    D                    1/4/1999               AA

       

       

       

      How can I do this?

       

      Need help urgently.

        • 1. Re: Compare two data sets from Excel and display details of the difference between two sets...
          Ashish Chaudhari

          Hi Parag,

           

          This is what I could manage. let me know if this helps in some way. While creating action from difference it takes all the 3 employees, which I couldn't fix.

           

          Dashboard

          Output (After action)

          Ivan Young, can probably the best person to help us out.

           

          Thanks and Regards,

          Ashish Chaudhari

          • 2. Re: Compare two data sets from Excel and display details of the difference between two sets...
            Ivan Young

            Hi Parag and Ashish,

            Thanks for pinging me, I'm on 9.2 so I can't open your twbx Ashsih.  I'm not entirely clear on how the two data sets are related  but I'm going to assume that we are blending.  In order to generate the desired output using blending we are going to make Excel 1 the Primary and Excel 2 the Secondary data source.

             

            Once the data sources are set up we create our Measures.

             

            Primary Name Count: COUNTD([Name]) - Create this as a Measure for Excel1

             

            Secondary Name Count:  COUNTD([Name]) = Create this a Measure for Excel2

             

            Not in Secondary: [Primary Name Count] - [Sheet2 (Not In)].[Seconday Name Count] - Create this as a Measure for Excel1

             

            We then create our source sheet. - When we select a measure we want to display the records that are in the Excel 1 with no match in Excel 2.

             

             

            We then create our target sheet.

             

            The trick to getting the output correct is to use the secondary data source Name as an exclude filter.  We will exclude all non-null values.

             

             

            Then we put it together on a dashboard and create our action filter.

             

            Attached is a twbx.  Let me know if you have any questions.


            Regards,

            Ivan