Create a Flag

[#1]

  T1:  

Id	Name	Status	Risk	Date
100	ABC	Open	Low	10/1/2014
100	ABC	Closed	Medium	3/2/2014
200	DEF	Open	Low	1/1/2015
200	DEF	Open	Medium	1/2/2015
200	DEF	Closed	High	1/3/2015
300	XXX	Open	High	5/6/2015
300	XXX	Open	Low	7/7/2015
300	XXX	Closed	High	8/9/2015
400	YYY	Open	Low	1/2/2015
400	YYY	Closed	Medium	2/2/2015
500	ZZZ	Open	Low	2/3/2015
100	ABC	Closed	High	3/5/2014
100	ABC	Closed	Medium	3/3/2014
200	DEF	Closed	Low	1/4/2015

I have a Table T1 with above mentioned data

we need to show the Risk  for a Id changes from when we first receive the Id's risk  in the above table to when their Status changes to Closed

A flag needs to be added to know risk changes or not

Output:

Id	Name	Status	Risk	Date	Risk_Flag
100	ABC	Open	Low	10/1/2014	N
100	ABC	Open	Medium	3/2/2014	Y
100	ABC	Open	High	3/5/2014	Y
100	ABC	Closed	Medium	3/6/2014	Y
200	DEF	Open	Low	1/1/2015	N
200	DEF	Open	Medium	1/2/2015	Y
200	DEF	Open	High	1/3/2015	Y
200	DEF	Closed	Low	1/4/2015	Y
300	XXX	Open	High	5/6/2015	N
300	XXX	Open	Low	7/7/2015	Y
300	XXX	Closed	High	8/9/2015	Y
400	YYY	Open	Low	1/2/2015	N
400	YYY	Closed	Medium	2/2/2015	Y
500	ZZZ	Open	Low	2/3/2015	N

Please help me to get the above output.

[#2]

We need to show the Risk  for a Id changes from when the  Status is Open in the above table to when their Status changes to Closed

Sorry for the Incorrect table T1.

Here is the updated table T1 with data:  

Id	Name	Status	Risk	Date
100	ABC	Open	Low	10/1/2014
100	ABC	Open	Medium	3/2/2014
200	DEF	Open	Low	1/1/2015
200	DEF	Open	Medium	1/2/2015
200	DEF	Open	High	1/3/2015
300	XXX	Open	High	5/6/2015
300	XXX	Open	Low	7/7/2015
300	XXX	Closed	High	8/9/2015
400	YYY	Open	Low	1/2/2015
400	YYY	Closed	Low	2/2/2015
500	ZZZ	Open	Low	2/3/2015
100	ABC	Open	High	3/5/2014
100	ABC	Closed	Medium	3/6/2014
200	DEF	Closed	Low	1/4/2015

The result would be

Fo ex:In the below result the risk has been changed from low->Medium->High->Medium
for a profile 100

100 low                     -

100 low->Medium    Y(changed)

100 Medium->High   Y(changed)

100 High->Medium   Y(changed)

Id	Name	Status	Risk	Date	Risk_Flag
100	ABC	Open	Low	10/1/2014	N
100	ABC	Open	Medium	3/2/2014	Y
100	ABC	Open	High	3/5/2014	Y
100	ABC	Closed	Medium	3/6/2014	Y
200	DEF	Open	Low	1/1/2015	N
200	DEF	Open	Medium	1/2/2015	Y
200	DEF	Open	High	1/3/2015	Y
200	DEF	Closed	Low	1/4/2015	Y
300	XXX	Open	High	5/6/2015	N
300	XXX	Open	Low	7/7/2015	Y
300	XXX	Closed	High	8/9/2015	Y
400	YYY	Open	Low	1/2/2015	N
400	YYY	Closed	Low	2/2/2015	N
500	ZZZ	Open	Low	2/3/2015	N

[#3]

We need to show the Risk  for a Id changes from when the  Status is Open in the above table to when their Status changes to Closed

Sorry for the Incorrect table T1.

Here is the updated table T1 with data:  

Id	Name	Status	Risk	Date
100	ABC	Open	Low	10/1/2014
100	ABC	Open	Medium	3/2/2014
200	DEF	Open	Low	1/1/2015
200	DEF	Open	Medium	1/2/2015
200	DEF	Open	High	1/3/2015
300	XXX	Open	High	5/6/2015
300	XXX	Open	Low	7/7/2015
300	XXX	Closed	High	8/9/2015
400	YYY	Open	Low	1/2/2015
400	YYY	Closed	Low	2/2/2015
500	ZZZ	Open	Low	2/3/2015
100	ABC	Open	High	3/5/2014
100	ABC	Closed	Medium	3/6/2014
200	DEF	Closed	Low	1/4/2015

The result would be

Fo ex:In the below result the risk has been changed from low->Medium->High->Medium
for a profile 100

100 low                     -

100 low->Medium    Y(changed)

100 Medium->High   Y(changed)

100 High->Medium   Y(changed)

Id	Name	Status	Risk	Date	Risk_Flag
100	ABC	Open	Low	10/1/2014	N
100	ABC	Open	Medium	3/2/2014	Y
100	ABC	Open	High	3/5/2014	Y
100	ABC	Closed	Medium	3/6/2014	Y
200	DEF	Open	Low	1/1/2015	N
200	DEF	Open	Medium	1/2/2015	Y
200	DEF	Open	High	1/3/2015	Y
200	DEF	Closed	Low	1/4/2015	Y
300	XXX	Open	High	5/6/2015	N
300	XXX	Open	Low	7/7/2015	Y
300	XXX	Closed	High	8/9/2015	Y
400	YYY	Open	Low	1/2/2015	N
400	YYY	Closed	Low	2/2/2015	N
500	ZZZ	Open	Low	2/3/2015	N

[#4]

We need to show the Risk  for a Id changes from when the  Status is Open in the above table to when their Status changes to Closed

Sorry for the Incorrect table T1.

Here is the updated table T1 with data:  

Id	Name	Status	Risk	Date
100	ABC	Open	Low	10/1/2014
100	ABC	Open	Medium	3/2/2014
200	DEF	Open	Low	1/1/2015
200	DEF	Open	Medium	1/2/2015
200	DEF	Open	High	1/3/2015
300	XXX	Open	High	5/6/2015
300	XXX	Open	Low	7/7/2015
300	XXX	Closed	High	8/9/2015
400	YYY	Open	Low	1/2/2015
400	YYY	Closed	Low	2/2/2015
500	ZZZ	Open	Low	2/3/2015
100	ABC	Open	High	3/5/2014
100	ABC	Closed	Medium	3/6/2014
200	DEF	Closed	Low	1/4/2015

The result would be

Fo ex:In the below result the risk has been changed from low->Medium->High->Medium
for a profile 100

100 low                     -

100 low->Medium    Y(changed)

100 Medium->High   Y(changed)

100 High->Medium   Y(changed)

Id	Name	Status	Risk	Date	Risk_Flag
100	ABC	Open	Low	10/1/2014	N
100	ABC	Open	Medium	3/2/2014	Y
100	ABC	Open	High	3/5/2014	Y
100	ABC	Closed	Medium	3/6/2014	Y
200	DEF	Open	Low	1/1/2015	N
200	DEF	Open	Medium	1/2/2015	Y
200	DEF	Open	High	1/3/2015	Y
200	DEF	Closed	Low	1/4/2015	Y
300	XXX	Open	High	5/6/2015	N
300	XXX	Open	Low	7/7/2015	Y
300	XXX	Closed	High	8/9/2015	Y
400	YYY	Open	Low	1/2/2015	N
400	YYY	Closed	Low	2/2/2015	N
500	ZZZ	Open	Low	2/3/2015	N

[sunny_talwar]

Try this:

Table:

LOAD Id,

     Name,

     Status,

     Risk,

     Date

FROM

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

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

FinalTable:

LOAD *,

  If(Id = Peek('Id'), If(Risk <> Peek('Risk'), 'Y', 'N'), 'N') as Risk_Flag

Resident Table

Order By Id, Date;

DROP Table Table;

[Kushal_Chawda]

Data:

LOAD Id,

    Name,

    Status,

    Risk,

    Date

FROM

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

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

Final:

LOAD Id,

    if(Id <> previous(Id) or  (Id = previous(Id) and Risk= previous(Risk)),'N','Y') as Risk_Flag,

    Name,

    Status,

    Risk,

    Date

Resident Data

order by Id, Date;

Drop Table Data;

[#7]

  Could you please help me to get the same output if we have data like below  

Id	Name	Status	Risk	Date
100	ABC	Open	Low	10/1/2014
100	ABC	Open	Medium	10/1/2014
200	DEF	Open	Low	1/1/2015
200	DEF	Open	Medium	1/1/2015
200	DEF	Open	High	1/1/2015
300	XXX	Open	High	5/6/2015
300	XXX	Open	Low	5/6/2015
300	XXX	Closed	High	8/9/2015
400	YYY	Open	Low	1/2/2015
400	YYY	Closed	Low	2/2/2015
500	ZZZ	Open	Low	2/3/2015
100	ABC	Open	High	10/1/2014
100	ABC	Closed	Medium	3/6/2014
200	DEF	Closed	Low	1/4/2015

[#8]

Could you please help me to get the same output if we have data like below  

Id	Name	Status	Risk	Date
100	ABC	Open	Low	10/1/2014
100	ABC	Open	Medium	10/1/2014
200	DEF	Open	Low	1/1/2015
200	DEF	Open	Medium	1/1/2015
200	DEF	Open	High	1/1/2015
300	XXX	Open	High	5/6/2015
300	XXX	Open	Low	5/6/2015
300	XXX	Closed	High	8/9/2015
400	YYY	Open	Low	1/2/2015
400	YYY	Closed	Low	2/2/2015
500	ZZZ	Open	Low	2/3/2015
100	ABC	Open	High	10/1/2014
100	ABC	Closed	Medium	3/6/2014
200	DEF	Closed	Low	1/4/2015

[Kushal_Chawda]

It will work for this data also as far as the logic is not changed.

[sunny_talwar]

What is the difference between this data and previously shared data?

[#11]

Previously,we have different dates on various risk ratings,status and we were ordering on Id,Date

currently,we have same date on open status and for different risk raings and the date changed on closed status