topic Re: Need help for writing expression in QlikView

Need help for writing expression

Fri, 12 Dec 2014 05:26:08 GMT

Hi ,

I want to write expression as to how many employees are reporting to particular manager. If there are no reportees then 0 should be displayed otherwise count of repotees should be displayed in straight chart..

Please help me ...

Re: Need help for writing expression

tresB — Fri, 12 Dec 2014 05:27:43 GMT

Like this?

Dimension: manager

Exp: Count( Distinct employees)

Re: Need help for writing expression

jyothish8807 — Fri, 12 Dec 2014 05:43:58 GMT

Hi Lokeshwari,

You can try what tresesco have suggested or you can use Aggr function in complex case. Can you share your app? It will be helpful to understand better.

Regards

Re: Need help for writing expression

tyagishaila — Fri, 12 Dec 2014 05:45:42 GMT

Hi Lokeshwari,

You can do it by using Pivot table,

Dim1: ManagerName

Dim2: EmployeeName

Expression: count(EmployeeName)

and apply show partial sum on Dim2,

Try it

Re: Need help for writing expression

Fri, 12 Dec 2014 05:47:40 GMT

may this helps you

if(Count( Distinct employees)>0,Count( Distinct employees),0)

Re: Need help for writing expression

tyagishaila — Fri, 12 Dec 2014 05:54:59 GMT

Hi,

For Straight Table,

Dim1: Manager

Dim2: Employee

Exp is

count(Total <Manger>Employee)

And for Null or Missing Values, Change '-' into 0 in Properties->Presentation

I am sure it will work

Re: Need help for writing expression

khalander — Fri, 12 Dec 2014 06:24:26 GMT

Hi,

Use AGGR() it will work .

aggr( distinct count(empID), MngrID)

Regards,

khalander

Re: Need help for writing expression

Fri, 12 Dec 2014 06:26:47 GMT

But still it is not giving correct value... I have an example as below

EMp:

LOAD * INLINE [

EMPno, EmpName, MangerID

1, A, 2

2, B

3, C, 4

4, D, 2

5, E, 4

6, F, 5

];

But in chart when i applied the expression it is coming like this

For Empno--1 Reportees 0 should come but it is diplaying as 1

Re: Need help for writing expression

Fri, 12 Dec 2014 06:30:23 GMT

But It is not working .Here is my data in script

EMp:

LOAD * INLINE [

EMPno, EmpName, MangerID

1, A, 2

2, B

3, C, 4

4, D, 2

5, E, 4

6, F, 5

];

For example for Empno-1 the number of reportees should come 0 since he is not manager to any of the

employees..

Re: Need help for writing expression

jyothish8807 — Fri, 12 Dec 2014 06:32:30 GMT

try:

if(len(trim(Reporte))>0,count(Reporte),0)

Regards

Re: Need help for writing expression

Fri, 12 Dec 2014 06:41:04 GMT

PFA

as per your data eno: 1 has manager id , and eno2 should be 0 as there is no manager id

Re: Need help for writing expression

tyagishaila — Fri, 12 Dec 2014 06:58:27 GMT

Immediate Try this in your expression

if(MangerID='' ,'',count(TOTAL <MangerID>EmpName))

'' means NULL

Re: Need help for writing expression

Fri, 12 Dec 2014 07:10:54 GMT

Please find the data

EMp:

LOAD * INLINE [

EMPno, EmpName, MangerID

1, A, 2

2, B

3, C, 4

4, D, 2

5, E, 4

6, F, 5

];

and I want to show the result using straight chart

Empno	No of Reportees
1	0
2	2
3	0
4	2
5	1
6	0

ManagerId will be same as the Empno

Re: Need help for writing expression

Fri, 12 Dec 2014 07:15:20 GMT

Still not clear with your data

pfa

may helps you

Re: Need help for writing expression

tyagishaila — Fri, 12 Dec 2014 07:19:54 GMT

LOAD * INLINE [

EmpNo, EmpName, MangerID

1, A, 2

2, B,

3, C, 4

4, D, 2

5, E, 4

6, F, 5

7, S, 2

];

I find this Output and I thin this is okay,

there are 3 employees who report to managerID 2

similarly 2 employees report to managerID 4

What Output you exactly want.

Its not clear

Re: Need help for writing expression

tyagishaila — Fri, 12 Dec 2014 07:23:28 GMT

Plz share your complete data,

I think there are more information which is not clear to us,

Re: Need help for writing expression

tyagishaila — Fri, 12 Dec 2014 07:28:19 GMT

How can be no of reportees 0 for empno 1 ,

while empno 1 reports to ManagerID 2 and also empno 4 reports to managerID 2,

so for empno 1 there should be 2 no of reportees,

Re: Need help for writing expression

tresB — Fri, 12 Dec 2014 07:29:37 GMT

A self-join would be useful, try like:

Input:
LOAD * INLINE [

EMPno, EmpName, MangerID

    1, A, 2
    2, B
    3, C, 4
    4, D, 2
    5, E, 4
    6, F, 5

];
Left Join
Load
MangerID as EMPno,
EMPno as Manager
Resident Input;

PFA

Re: Need help for writing expression

Fri, 12 Dec 2014 07:30:22 GMT

For Empno 1 is not in MgrId so he won't be having any reportees so count of Reprotees will be 0

similarly Empno 2 there are 3 employees reporting to him.. EmpNo 1,4,7 So count of Reprotees will be 3

Like wise for all employees i want..

Is this clear now?

Re: Need help for writing expression

Fri, 12 Dec 2014 07:38:18 GMT

Thank you very much tresesco

Thanks all for your replies..