Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

timsaddler
Contributor 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
MVP & Luminary
MVP & Luminary

Re: Getting rid of duplicate records

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
3 Replies
MVP & Luminary
MVP & Luminary

Re: Getting rid of duplicate records

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
Contributor III

Re: Getting rid of duplicate records

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

Thanks a lot

MVP & Luminary
MVP & Luminary

Re: Getting rid of duplicate records

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