tracking changes in a column

[#1]

Hello All,

I have a problem if someone can help me with. I have a table something like below

item	Date	Status
A	01/02/2016	ON_STATUS
A	01/04/2016	ON_STATUS
A	01/05/2016	OFF_STATUS
B	01/03/2016	ON_STATUS
B	01/05/2016	OFF_STATUS
B	01/06/2016	ONSTATUS
B	01/08/2016	OFF_STATUS

I want to track the changes to the status for each item when it goes from ON_STATUS to OFF_STATUS. So for item A, status changed from ON_STATUS to OFF_STATUS only once whereas for B it changed from ON to OFF twice. Is there a way to do this?

thanks,

Manoj Agrawal

[swuehl]

I am not exactely sure how you want to track the changes, maybe you can use something like

LOAD *,

     Autonumber(If(Peek(Status)LIKE 'ON*' and Status LIKE 'OFF*',Date), item) as CountOff;

LOAD item,

     Date,

     Status

FROM

[https://community.qlik.com/thread/228012]

(html, codepage is 1252, embedded labels, table is @1);

[#3]

Hi Stefan, thanks for replying but it doesn't work. The way I want to track status for each item is that I should get the results as

Item          No. changes in status from ON to OFF

A                1

B                2

hope this makes the requirements clear.

regards,

Manoj

[sunny_talwar]

Using Stefan's code you can do Count(CountOff)

[sunny_talwar · Accepted solution]

Slight change to Stefan's code (which you might need because the data may or may not be well sorted for you)

Table:

LOAD item,

    Date,

    Status

FROM

[https://community.qlik.com/thread/228012]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

LOAD *,

    If(item = Previous(item),

    If(Peek(Status) LIKE 'ON*' and Status LIKE 'OFF*', 1, 0), 0) as CountOff

Resident Table

Order By item, Date;

DROP Table Table;

[#6]

Sunny, I have personal edition so can't view your file. I tried the code you have pasted but I am getting zeros as change status for all the items. Could you please paste the actual code you have used in the file?

thanks,

Manoj

[sunny_talwar]

The pasted the code and the expression is just Sum(Countoff). Not sure what else you are looking for.

[#8]

okay. Let me try again. Maybe I am not using the code correctly.

thanks,

Manoj

[#9]

Didn't realize I have to create another table to add all the counters. Works great.

thanks,

Manoj