3 Replies Latest reply: Jul 15, 2015 4:36 AM by jagan mohan rao appala RSS

    Getting rid of duplicate records

    Tim Saddler

      Hi all

      I have a problem where data is being merged within a Qlikview app where the data is entered at 2 different levels in the hierarchy.

      The higher level is Factory (CC38)

      Business unit is a child of factory.

      as a result I need to only produce a single record at CC38 (Factory level) whereas all  other data is entered at the lower Business Unit level.

      I enclose some example data which should make my issue more understandable.

       

      Many thanks in advance for advice you can give me.

        • Re: Getting rid of duplicate records
          Gysbert Wassenaar

          Something like this:

           

          LOAD IncidentId,

               IncidentDate,

               Incident.Title,

               IncidentCategory,

               IncidentCategoryDesc,

               InvolvementTypeId,

               InvolvementTypeIdDesc,

               PersonId,

               RelationshipIdDesc,

               Person.FirstName,

               CC38,

               FirstValue([Business_Unit]) as [Business_Unit],

               [Incident Count]

          FROM[Duplicate_records.xlsx]

          (ooxml, embedded labels, table is [Current Data])

          Where IncidentId

          Group BY IncidentId,

               IncidentDate,

               Incident.Title,

               IncidentCategory,

               IncidentCategoryDesc,

               InvolvementTypeId,

               InvolvementTypeIdDesc,

               PersonId,

               RelationshipIdDesc,

               Person.FirstName,

               CC38,

               [Incident Count]

          ;

          • Re: Getting rid of duplicate records
            jagan mohan rao appala

            Hi,

             

            Try like this using Group By and Maxstring()

             

            Data:

            LOAD IncidentId,

                 IncidentDate,

                 Incident.Title,

                 IncidentCategory,

                 IncidentCategoryDesc,

                 InvolvementTypeId,

                 InvolvementTypeIdDesc,

                 PersonId,

                 RelationshipIdDesc,

                 Person.FirstName,

                 CC38,     

                 [Incident Count],

                 MaxString(Business_Unit) AS  Business_Unit

            FROM

            [Duplicate_records.xlsx]

            (ooxml, embedded labels, table is [Current Data])

            WHERE Len(Trim(IncidentId)) > 0

            GROUP BY IncidentId,

                 IncidentDate,

                 Incident.Title,

                 IncidentCategory,

                 IncidentCategoryDesc,

                 InvolvementTypeId,

                 InvolvementTypeIdDesc,

                 PersonId,

                 RelationshipIdDesc,

                 Person.FirstName,

                 CC38,     

                 [Incident Count];