topic Re: Create a Flag in QlikView

Create a Flag

Wed, 11 May 2016 09:27:39 GMT

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.

Re: Create a Flag

MK_QSL — Wed, 11 May 2016 09:32:54 GMT

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;

Re: Create a Flag

puttemans — Wed, 11 May 2016 09:35:50 GMT

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

Re: Create a Flag

sunny_talwar — Wed, 11 May 2016 09:37:26 GMT

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;

Re: Create a Flag

avinashelite — Wed, 11 May 2016 09:40:32 GMT

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

Re: Create a Flag

Kushal_Chawda — Wed, 11 May 2016 09:41:05 GMT

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;

Re: Create a Flag

veeranj — Wed, 11 May 2016 09:48:00 GMT

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

Re: Create a Flag

Wed, 11 May 2016 10:15:44 GMT

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

Re: Create a Flag

Wed, 11 May 2016 10:16:52 GMT

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

Re: Create a Flag

Wed, 11 May 2016 10:17:11 GMT

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

Re: Create a Flag

Wed, 11 May 2016 10:17:27 GMT

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

Re: Create a Flag

Wed, 11 May 2016 10:17:57 GMT

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

Re: Create a Flag

Wed, 11 May 2016 10:18:15 GMT

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

Re: Create a Flag

sunny_talwar — Wed, 11 May 2016 10:19:41 GMT

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;

Re: Create a Flag

Kushal_Chawda — Wed, 11 May 2016 10:19:47 GMT

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;

Re: Create a Flag

Wed, 11 May 2016 10:54:56 GMT

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

Re: Create a Flag

Wed, 11 May 2016 10:55:26 GMT

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

Re: Create a Flag

Kushal_Chawda — Wed, 11 May 2016 10:57:10 GMT

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

Re: Create a Flag

sunny_talwar — Wed, 11 May 2016 10:57:26 GMT

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

Re: Create a Flag

Wed, 11 May 2016 11:14:39 GMT

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