2 Replies Latest reply on Apr 8, 2016 1:45 AM by jens.bruckmann

    duplicate entries with calculated field

    jens.bruckmann

      hi all,

       

      I am getting my Data out of an Excel file. As this excel file is updated nearly weekly it is hard to modify this every week so I was thinking about a formula to get this automated.

       

      In this exel file there is a row which is called Site ID.

       

      In this row it is possible to have the Site ID twice but the names are differnet in the Row Site Name:

       

      So I though as an Idea I can use foolowing formula:

       

      If [Site ID] = "2080" then [Site Name]="this site" else [Site Name] end

       

      but this is not working

        • 1. Re: duplicate entries with calculated field
          Carl Slifer

          Hi Jens,

           

          You will run into a lot of issues here because of blending. When you blend data all the data from one side of the blend and all the data from the other side of the blend are related based on the field you've chosen as a relationship. When showing dimensions in the blend afterwards (putting blue things on the rows shelf) we are only able to match identical fields on both sides.  So if the primary blend has inside of the blended field an alias (let's call it) Alpha and that same Alpha is related to Alpha on the secondary data source but then there are two different field in the secondary you're unable to see them both. Because you cannot aggregate two different dimensions and anything from a secondary data source must be aggregated.

           

          PRIMARY           

          Blending Field

          Alpha

          Beta

           

          SECONDARY

          Blending Field     Extra Field           

          Alpha                    Chicken

          Alpha                    Turkey

          Beta                      Squirrel   

           

          When you put Alpha on the rows shelf from each everything will match but if you put extra field on you will get

          Alpha     Alpha     *

          Beta     Beta          Squirrel

           

          The way around this will be to find a truly unique identifier. If things in the one of the data sources are not truly unique for each site id or site name then you will continue to have trouble. Alternatively you could bring these the same excel workbook which would allow for the controlled usage of joins or custom sql.

           

           

          Cheers!

          Carl Slifer

          InterWorks

          • 2. Re: duplicate entries with calculated field
            jens.bruckmann

            Hi Carl,

             

            thanks a lot for the answer. So at the end without Raw Data manipulation I do not have a chance to get this sorted. Ok I will make some thoughts how I can get this Site ID Unique.

             

            Thanks a lot for your help