Getting rid of duplicate records

[timsaddler]

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.

[Gysbert_Wassenaar · Accepted solution]

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]

;

[timsaddler]

Great answer - just couldn't "visualise" the FirstValue function

Thanks a lot

[jagan]

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];