topic Re: Show only data without duplicate in QlikView

Show only data without duplicate

Anonymous — Fri, 27 Jun 2014 04:05:25 GMT

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,

Re: Show only data without duplicate

SunilChauhan — Fri, 27 Jun 2014 04:11:39 GMT

dimension

Employee

status

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

hope this helps

Re: Show only data without duplicate

PrashantSangle — Fri, 27 Jun 2014 04:32:08 GMT

Hi,

Use like this,

Dimension,

Employee:

expression :

if(Count(WorkID)=1, WorkID)

and check Supress null value.

Regards,

Re: Show only data without duplicate

Fri, 27 Jun 2014 05:06:50 GMT

Hi,

May be try like this below,

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

Re: Show only data without duplicate

Anonymous — Fri, 27 Jun 2014 05:07:10 GMT

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;

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

drop table 1;
drop Field k;

use this..

HTH

Ravi N.

Re: Show only data without duplicate

kamalqlik — Fri, 27 Jun 2014 05:42:17 GMT

Hi Ahmad,

Please Find attached QVW for your refrence.

Regards

Kamal Naithani

Re: Show only data without duplicate

Anonymous — Fri, 27 Jun 2014 06:23:24 GMT

Hi, can you just copy the code here?Tq

Re: Show only data without duplicate

its_anandrjs — Fri, 27 Jun 2014 06:28:35 GMT

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

Re: Show only data without duplicate

kamalqlik — Fri, 27 Jun 2014 06:42:29 GMT

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

Re: Show only data without duplicate

Fri, 27 Jun 2014 07:10:21 GMT

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

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

Re: Show only data without duplicate

SunilChauhan — Fri, 27 Jun 2014 07:17:36 GMT

in dimension take Employee

and in expression:

if(Count( distinct WorkID )=1,Status)

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

hope this helps