script to count

[arethaking]

Hi All,

I have a Employee Status table.

DATA:

LOAD * INLINE [EMPNO,ENAME,STATUS_A,STATUS_B,STATUS_C

100,E1,0,0,1

200,E2,0,1,0

300,E3,1,0,0

400,E4,0,1,1

500,E5,1,1,0

600,E6,0,0,1

];

Need to write the script to show below details.

Count(STATUS_A)=2

Count(STATUS_B)=3

Count(STATUS_A)=3

SUM(STATUS_A+STATUS_B+STATUS_C)=8

Thanks in advance.

[sunny_talwar]

Where do you want to do this? Script:

DATA:

LOAD * INLINE [EMPNO,ENAME,STATUS_A,STATUS_B,STATUS_C

100,E1,0,0,1

200,E2,0,1,0

300,E3,1,0,0

400,E4,0,1,1

500,E5,1,1,0

600,E6,0,0,1

];

AggrData:

LOAD Sum(STATUS_A) as CountSTATUS_A,

  Sum(STATUS_B) as CountSTATUS_B,

  Sum(STATUS_C) as CountSTATUS_C,

  RangeSum(Sum(STATUS_A), Sum(STATUS_B), Sum(STATUS_C)) as TotalCount

Resident DATA;

[arethaking]

Hi Sunny,

Thanks for your quick reply.

I added new field Product , derived new field STATUS.

Now it is not showing correct data.

When STATUS=A selected sum(STATUS_A where Product=P1)=1

When STATUS=B selected sum(STATUS_B where Product=P1)=1

When STATUS=C selected sum(STATUS_B where Product=P1)=3

Please help me.

DATA:

LOAD * INLINE [EMPNO,ENAME,STATUS_A,STATUS_B,STATUS_C,Product

100,E1,0,0,1,P1

200,E2,0,1,0,P1

300,E3,1,0,0,P1

400,E4,0,0,1,P1

500,E5,1,0,0,P2

600,E6,0,0,1,P2

];

AggrData:

LOAD EMPNO,ENAME,Product,

if(Sum(STATUS_A)>=1,'A',If(Sum(STATUS_B)>=1,'B',if(Sum(STATUS_C)>=1,'C'))) as STATUS,

  Sum(STATUS_A) as CountSTATUS_A,

  Sum(STATUS_B) as CountSTATUS_B,

  Sum(STATUS_C) as CountSTATUS_C,

  RangeSum(Sum(STATUS_A), Sum(STATUS_B), Sum(STATUS_C)) as TotalCount

Resident DATA

Group by EMPNO,ENAME,Product;

drop table DATA;

[jagan · Accepted solution]

Hi,

Try like this

DATA:

CrossTable(Status, Count, 3)

LOAD

EMPNO,ENAME,Product, STATUS_A,STATUS_B,STATUS_C

INLINE [

EMPNO,ENAME,STATUS_A,STATUS_B,STATUS_C,Product

100,E1,0,0,1,P1

200,E2,0,1,0,P1

300,E3,1,0,0,P1

400,E4,0,0,1,P1

500,E5,1,0,0,P2

600,E6,0,0,1,P2

];

[jagan]

Missed to attach set analysis expression

Set Analysis Expression:

=Count({<Count={'1'}>}Status)

Regards,

Jagan.