Show only data without duplicate

[Anonymous]

Employee               WorkID      Status

Adam                            1          Missing

Alex                              2          Approved

LIm                               3          Pending

Raja                              4          Pending

Singh                            5           Approved   

Adam                            6          Missing

Alex                              7          Approved

Lim                               8          Approved

What formula to use. If I want to produce below result. I dont want employee that have more than 1 id:

Employee          WorkID          Status

Lim                      3                    Pending

Raja                     4                    Pending

Singh                    5                   Approved

TQ,

[SunilChauhan]

dimension

Employee

status

expression --> if(Count( distinct  WorkID  )=1,Count( distinct  WorkID  ))

hope this helps

[PrashantSangle]

Hi,

Use like this,

Dimension,

Employee:

expression :

if(Count(WorkID)=1, WorkID)

and check Supress null value.

Regards,

[#4]

Hi,

May be try like this below,

if( workid=1, aggr(count(distinct Workid) , Employee)

[Anonymous]

Hi

1:

load * inline
[
Employee,WorkID,Status
Adam,1,Missing
Alex,2,Approved
Lim,3,Pending
Raja,4,Pending
Singh,5,Approved
Adam,6,Missing
Alex,7,Approved
Lim,8,Approved
];
inner join
load Count(WorkID) as c,Employee
Resident 1 Group by Employee;

2:

Load *,'i' as k
Resident 1 where c=1;

drop table 1;
drop Field k;

use this..

HTH

Ravi N.

[kamalqlik]

Hi Ahmad,

Please Find attached QVW for your refrence.

Regards

Kamal Naithani

[Anonymous]

Hi, can you just copy the code here?Tq

[its_anandrjs]

Hi,

Without affecting the source table you can try with the below load script

Tab1:

LOAD * INLINE [

    Employee, WorkID, Status

    Adam, 1, Missing

    Alex, 2, Approved

    LIm, 3, Pending

    Raja, 4, Pending

    Singh, 5, Approved

    Adam, 6, Missing

    Alex, 7, Approved

    Lim, 8, Approved

];

IdCount:

LOAD

Employee,

Status as NewStatus,

Count(WorkID) as CountWork

Resident Tab1

Group By Employee,Status;

NoConcatenate

New:

LOAD

Employee,

NewStatus,

CountWork

Resident IdCount

Where CountWork = 1;

DROP Table IdCount;

Left Join

LOAD

Employee, WorkID as NewWorkid

Resident Tab1;

And then in a chart plot

Employee,NewWorkid,NewStatus

Or in the front end you can try with as calculated dimension

=if(Aggr(Count(WorkID)=1, Employee), Employee) //and suppress when value is null selected

[kamalqlik]

Hi Ahmad,

EmployeeTable:

load * inline

[

Employee,WorkID,Status

Adam,1,Missing

Alex,2,Approved

Llm,3,Pending

Raja,4,Pending

Singh,5,Approved

Adam,6,Missing

Alex,7,Approved

Lim,8,Approved

];

Inner Join

Employee1:

load if(count(DISTINCT WorkID)=1,count(distinct WorkID)) as newworkid,Employee

Resident EmployeeTable Group by Employee

;

Employee2:

load *,

'TestEmployee' as flag

Resident EmployeeTable

Where newworkid='1';

DROP Table EmployeeTable;

Regards

Kamal Naithani

[#10]

hi

take Employee,Status as a dimension and write this expression:

if(Count(DISTINCT Employee)=1,WorkID);