count status

[surajap123]

Hi Experts,

I want to count the status for each day by id. Each status has a validity period. So the validity expires when status changes.

Eg-
For id 100, the status 'A' is valid till 14/04/2016. So from 01/01/2016 to 14/04/2016 the count of status A should be 1 for id 100.

Data:
LOAD * INLINE [
    id, Status, EndDate
    100, A, 01/01/2016
    100, A, 05/02/2016
    100, B, 15/04/2016
    200, A, 05/01/2016
    200, B, 10/03/2016
    200, B, 13/05/2016
];

Please let me know how to achive this.

[tresesco]

Try like:

Straight table

Dim1: id

Dim2: Date

Exp: Count( Distinct Status)

Select Status 'A' for it's count. And if you want always Status 'A', you can fix that in the expression like: Count( {<Status={'A'}>}Distinct Status)

In fact this doesn't make much sense. Or you are looking for something else ?

[MK_QSL]

How to know which id has validity date?

What I am understanding from below is

    100, A, 01/01/2016

    100, A, 05/02/2016

    100, B, 15/04/2016

for id 100 Status is changed on 15/04/2016 so the validity for status A is one day before it.

Means this line is not useful..
    100, A, 05/02/2016

[rubenmarin]

Hi suraj, maybe using a flag to check when the status has changed:

Data:

LOAD * INLINE [

    Id, Status, Date

    100, A, 01/01/2016

    100, A, 05/02/2016

    100, B, 15/04/2016

    200, A, 05/01/2016

    200, B, 10/03/2016

    200, B, 13/05/2016

];

Data2:

NoConcatenate

LOAD Id, Status, Date, If(IsNull(Peek(Id)) or (Peek(Id)=Id and Peek(Status)=Status), 1, 0) as flag

Resident Data order by Id, Date, Status;

DROP table Data;

And use the expression:

Count(TOTAL <Id, Status> {<flag={1}>} Status)

[MK_QSL]

If you want to count Status for each id for each day, use below..

Data:

LOAD * INLINE [

    id, Status, Date

    100, A, 01/01/2016

    100, A, 05/02/2016

    100, B, 15/04/2016

    200, A, 05/01/2016

    200, B, 10/03/2016

    200, B, 13/05/2016

];

Left Join (Data)

Load id, Status, Date, If(id <> previous(id), Date, Previous(Date)) as NextDate Resident Data

Order By id, Date Desc;

Final:

Load id, Status, Date(Date + IterNo() - 1) as Date Resident Data

While Date + IterNo() - 1 <= NextDate;

And you can follow expression of Tresesco and Ruben

[surajap123]

Thanks for all the inputs, I will get back after working on them.