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.

[MK_QSL]

Input:
LOAD Id,
     Name,
     Status,
     Risk,
     Date,
     Id & Name as Key
FROM
[https://community.qlik.com/thread/216412]
(html, codepage is 1252, embedded labels, table is @1);


Output:
Load
  Id,
  Name,
  Status,
  Risk,
  Date,
  If(Key = Previous(Key), 'Y','N') as Risk_Flag
Resident Input
Order By Key, Date;


Drop Table Input;
Drop Field Key;

[puttemans]

Hi,

Not sure I understand your question. What is the condition for the flag? In the above table, you have 'closed' status, and still 'y' as a risk flag. Also the risk field does not seem to play a role.

Regards,

Johan

[sunny_talwar · Accepted solution]

Is the ouput you mentioned above correct?

I am getting this

Using the following script:

Table:

LOAD Id,

    Name,

    Status,

    Risk,

    Date

FROM

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

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

FinalTable:

LOAD *,

  If(Id = Peek('Id'), If(Status = 'Closed', 'Y', Peek('Risk_Flag')), If(Status = 'Closed', 'Y', 'N')) as Risk_Flag

Resident Table

Order By Id, Date;

DROP Table Table;

[avinashelite]

Your Risk flag logic is based on the date or how it should be designed ...could you explain ??

[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),'N','Y') as Risk_Flag,

    Name,

    Status,

    Risk,

    Date

Resident Data

order by Id, Date;

Drop Table Data;

[veeranj]

Hi ,

You can try this .

If(id=previous(id) and Risk<>previous(Risk),'Y','N') as Flag

and you have to  do "order by Id " at the end of the load statement.

Thanks,

veera

[#8]

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

[#9]

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

[#10]

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