Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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]
;
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]
;
Great answer - just couldn't "visualise" the FirstValue function
Thanks a lot
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];