Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Re: Create a Flag

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;

31 Replies

Re: Create a Flag

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
Valued Contributor

Re: Create a Flag

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

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;

Re: Create a Flag

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

Re: Create a Flag

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;

anjeepvr
Contributor II

Re: Create a Flag

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

Re: Create a Flag

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

Re: Create a Flag

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

Re: Create a Flag

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
Community Browser