Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
timsaddler
Creator III
Creator III

Getting rid of duplicate records

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.

1 Solution

Accepted Solutions
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]

;


talk is cheap, supply exceeds demand

View solution in original post

3 Replies
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]

;


talk is cheap, supply exceeds demand
timsaddler
Creator III
Creator III
Author

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

Thanks a lot

jagan
Luminary Alumni
Luminary Alumni

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