31 Replies Latest reply: May 27, 2016 6:14 AM by Sunny Talwar RSS

    Create a Flag

    reddy r

        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.

        • Re: Create a Flag
          Manish Kachhia
          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
              reddy r

              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
            • Re: Create a Flag
              Puttemans Johan

              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
                  reddy r

                  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
                • Re: Create a Flag
                  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;

                    • Re: Create a Flag
                      reddy r

                      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
                    • Re: Create a Flag
                      Avinash R

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

                        • Re: Create a Flag
                          reddy r

                          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
                        • Re: Create a Flag
                          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;

                            • Re: Create a Flag
                              reddy r

                              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
                            • Re: Create a Flag
                              Veeranjaneyulu Pabbineedi

                              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
                                reddy r

                                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
                                • Re: Create a Flag
                                  Sunny Talwar

                                  Try this:

                                   

                                  Table:

                                  LOAD Id,

                                       Name,

                                       Status,

                                       Risk,

                                       Date,

                                       AutoNumber(RowNo(), Id) as Key

                                  FROM

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

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

                                   

                                  FinalTable:

                                  LOAD *,

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

                                  Resident Table

                                  Order By Id, Key;

                                   

                                  DROP Table Table;


                                  Capture.PNG