Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Create a Flag

  T1:  

IdNameStatusRiskDate
100ABCOpenLow10/1/2014
100ABCClosedMedium3/2/2014
200DEFOpenLow1/1/2015
200DEFOpenMedium1/2/2015
200DEFClosedHigh1/3/2015
300XXXOpenHigh5/6/2015
300XXXOpenLow7/7/2015
300XXXClosedHigh8/9/2015
400YYYOpenLow1/2/2015
400YYYClosedMedium2/2/2015
500ZZZOpenLow2/3/2015
100ABCClosedHigh3/5/2014
100ABCClosedMedium3/3/2014
200DEFClosedLow1/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:

   

IdNameStatusRiskDateRisk_Flag
100ABCOpenLow10/1/2014N
100ABCOpenMedium3/2/2014Y
100ABCOpenHigh3/5/2014Y
100ABCClosedMedium3/6/2014Y
200DEFOpenLow1/1/2015N
200DEFOpenMedium1/2/2015Y
200DEFOpenHigh1/3/2015Y
200DEFClosedLow1/4/2015Y
300XXXOpenHigh5/6/2015N
300XXXOpenLow7/7/2015Y
300XXXClosedHigh8/9/2015Y
400YYYOpenLow1/2/2015N
400YYYClosedMedium2/2/2015Y
500ZZZOpenLow2/3/2015N

Please help me to get the above output.

1 Solution

Accepted Solutions
sunny_talwar

Is the ouput you mentioned above correct?

I am getting this

Capture.PNG

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;

View solution in original post

31 Replies
MK_QSL
MVP
MVP

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
Specialist
Specialist

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

Is the ouput you mentioned above correct?

I am getting this

Capture.PNG

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
Creator II
Creator II

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

Not applicable
Author

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:  

IdNameStatusRiskDate
100ABCOpenLow10/1/2014
100ABCOpenMedium3/2/2014
200DEFOpenLow1/1/2015
200DEFOpenMedium1/2/2015
200DEFOpenHigh1/3/2015
300XXXOpenHigh5/6/2015
300XXXOpenLow7/7/2015
300XXXClosedHigh8/9/2015
400YYYOpenLow1/2/2015
400YYYClosedLow2/2/2015
500ZZZOpenLow2/3/2015
100ABCOpenHigh3/5/2014
100ABCClosedMedium3/6/2014
200DEFClosedLow1/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)

IdNameStatusRiskDateRisk_Flag
100ABCOpenLow10/1/2014N
100ABCOpenMedium3/2/2014Y
100ABCOpenHigh3/5/2014Y
100ABCClosedMedium3/6/2014Y
200DEFOpenLow1/1/2015N
200DEFOpenMedium1/2/2015Y
200DEFOpenHigh1/3/2015Y
200DEFClosedLow1/4/2015Y
300XXXOpenHigh5/6/2015N
300XXXOpenLow7/7/2015Y
300XXXClosedHigh8/9/2015Y
400YYYOpenLow1/2/2015N
400YYYClosedLow2/2/2015N
500ZZZOpenLow2/3/2015N
Not applicable
Author

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:  

IdNameStatusRiskDate
100ABCOpenLow10/1/2014
100ABCOpenMedium3/2/2014
200DEFOpenLow1/1/2015
200DEFOpenMedium1/2/2015
200DEFOpenHigh1/3/2015
300XXXOpenHigh5/6/2015
300XXXOpenLow7/7/2015
300XXXClosedHigh8/9/2015
400YYYOpenLow1/2/2015
400YYYClosedLow2/2/2015
500ZZZOpenLow2/3/2015
100ABCOpenHigh3/5/2014
100ABCClosedMedium3/6/2014
200DEFClosedLow1/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)

IdNameStatusRiskDateRisk_Flag
100ABCOpenLow10/1/2014N
100ABCOpenMedium3/2/2014Y
100ABCOpenHigh3/5/2014Y
100ABCClosedMedium3/6/2014Y
200DEFOpenLow1/1/2015N
200DEFOpenMedium1/2/2015Y
200DEFOpenHigh1/3/2015Y
200DEFClosedLow1/4/2015Y
300XXXOpenHigh5/6/2015N
300XXXOpenLow7/7/2015Y
300XXXClosedHigh8/9/2015Y
400YYYOpenLow1/2/2015N
400YYYClosedLow2/2/2015N
500ZZZOpenLow2/3/2015N
Not applicable
Author

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:  

IdNameStatusRiskDate
100ABCOpenLow10/1/2014
100ABCOpenMedium3/2/2014
200DEFOpenLow1/1/2015
200DEFOpenMedium1/2/2015
200DEFOpenHigh1/3/2015
300XXXOpenHigh5/6/2015
300XXXOpenLow7/7/2015
300XXXClosedHigh8/9/2015
400YYYOpenLow1/2/2015
400YYYClosedLow2/2/2015
500ZZZOpenLow2/3/2015
100ABCOpenHigh3/5/2014
100ABCClosedMedium3/6/2014
200DEFClosedLow1/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)

IdNameStatusRiskDateRisk_Flag
100ABCOpenLow10/1/2014N
100ABCOpenMedium3/2/2014Y
100ABCOpenHigh3/5/2014Y
100ABCClosedMedium3/6/2014Y
200DEFOpenLow1/1/2015N
200DEFOpenMedium1/2/2015Y
200DEFOpenHigh1/3/2015Y
200DEFClosedLow1/4/2015Y
300XXXOpenHigh5/6/2015N
300XXXOpenLow7/7/2015Y
300XXXClosedHigh8/9/2015Y
400YYYOpenLow1/2/2015N
400YYYClosedLow2/2/2015N
500ZZZOpenLow2/3/2015N