Qlik Community

Qlik DataMarket Discussions

Discussion Board for collaboration regarding Qlik DataMarket.

Highlighted
pooja821
Contributor

Handling Duplicate Record for one specific employee in Qliksense

Hi,

I have base which basically comprises of Employees and there respective projects by Name EMPLOYEE_ID and PROJECT_TYPE.

Now I have some cases where I have an employee id in one specific month which basically have two project type I.e.

for eg Employee 12345 lies in Project A as well as Project B in one month.

So when I m taking a count of employee corresponding to the project type then this employee gets counted in Project  A count as well as in Project B count, because of which I m getting double count of that employee, which ideally should not happen.

Now as solution, I'm trying to create a category as "Both" which will include both Project Type A & B in case employee has two Project Type in a specific month. Hence I would have Project Type A, B and Both.

Kindly help me on this.

1 Solution

Accepted Solutions

Re: Handling Duplicate Record for one specific employee in Qliksense

May be load your data like this

Table:

LOAD Date,

    [Employee ID],

    [Project Type]

FROM

[..\..\Downloads\Base.xlsx]

(ooxml, embedded labels, table is Sheet1);


Left Join (Table)

LOAD Date,

[Employee ID],

If(Count(DISTINCT [Project Type]) > 1, 'Both', Only([Project Type])) as [New Project Type]

Resident Table

Group By Date, [Employee ID];


Capture.PNG

8 Replies
pooja821
Contributor

Re: Handling Duplicate Record for one specific employee in Qliksense

Hi stalwar1‌ Kindly help on the above problem please.

Re: Handling Duplicate Record for one specific employee in Qliksense

So, what should happen if a single employee works on multiple projects? where should he be counted?

pooja821
Contributor

Re: Handling Duplicate Record for one specific employee in Qliksense

He should be counted in the different category called "BOTH" in project type.

Re: Handling Duplicate Record for one specific employee in Qliksense

Make sense, would you be able to share a sample to show what you have so that we can help you better here?

pooja821
Contributor

Re: Handling Duplicate Record for one specific employee in Qliksense

PFA file for the sample data. for instance, if I put filter on date for JUN and employee id 3456 then I get two project type i.e. A and B. So what I want is create a new column i.e. New Project Type which would have project type including BOTH category as well corresponding to employee id and date.

Re: Handling Duplicate Record for one specific employee in Qliksense

May be load your data like this

Table:

LOAD Date,

    [Employee ID],

    [Project Type]

FROM

[..\..\Downloads\Base.xlsx]

(ooxml, embedded labels, table is Sheet1);


Left Join (Table)

LOAD Date,

[Employee ID],

If(Count(DISTINCT [Project Type]) > 1, 'Both', Only([Project Type])) as [New Project Type]

Resident Table

Group By Date, [Employee ID];


Capture.PNG

pooja821
Contributor

Re: Handling Duplicate Record for one specific employee in Qliksense

m working in qliksense. Would really appreciate if you can share the QVF also.

Re: Handling Duplicate Record for one specific employee in Qliksense

Would you not be able to run this script in Qlik Sense? All you need to do is to change the file path....

Table:

LOAD Date,

    [Employee ID],

    [Project Type]

FROM

[..\..\Downloads\Base.xlsx]

(ooxml, embedded labels, table is Sheet1);


Left Join (Table)

LOAD Date,

[Employee ID],

If(Count(DISTINCT [Project Type]) > 1, 'Both', Only([Project Type])) as [New Project Type]

Resident Table

Group By Date, [Employee ID];