topic Re: tracking changes in a column in QlikView

tracking changes in a column

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: tracking changes in a column

swuehl — Thu, 04 Aug 2016 21:54:15 GMT

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);

Re: tracking changes in a column

Thu, 04 Aug 2016 22:24:21 GMT

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

Re: tracking changes in a column

sunny_talwar — Thu, 04 Aug 2016 22:53:59 GMT

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

Re: tracking changes in a column

sunny_talwar — Thu, 04 Aug 2016 22:58:37 GMT

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;

Re: tracking changes in a column

Thu, 04 Aug 2016 23:36:47 GMT

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

Re: tracking changes in a column

sunny_talwar — Thu, 04 Aug 2016 23:46:33 GMT

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

Re: tracking changes in a column

Thu, 04 Aug 2016 23:49:08 GMT

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

thanks,

Manoj

Re: tracking changes in a column

Fri, 05 Aug 2016 00:12:15 GMT

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

thanks,

Manoj