3 Replies Latest reply: Mar 15, 2016 1:07 PM by Stefan Wühl RSS

    How to create a new date check status?

    Thien Dieple

      All,

       

      I am using QlikSense and I want to create Status 2.

      It involves searching the same table to see if there was a status check done before the current status check.

      If there was not a previous status check in the same table, I would update the Status 2 field as 'No Check Performed'

       

       

      Account IDStatusDateStatus 2Notes
      1234535 Day Expired3/1/201635 Day ExpiredIf account status = '35 day expired', and the account has a prior status check, then keep Status 2 keeps value of '35 day expired'
      12345Conditional Approval2/10/2016Conditional ApprovalIf status does not equal "expired", then Status 2 keeps same value as Status
      6897435 Day Expired3/1/2016No Check PerformedIf account status = expired, and the account did not have another status check, then Status 2 becomes 'No Check Performed'

       

       

      Thanks for your help.

        • Re: How to create a new date check status?
          Stefan Wühl

          Maybe like

           

          LOAD

               [Account ID],

               [Status],

               [Date],

               [Account ID] as [AccountIDCheck],

               If( Status <> '35 Day Expired', Status, If(Exists([AccountIDCheck,[Account ID]), Status, 'No Check Performed')) as Status2

          RESIDENT YourTable

          ORDER BY Date;

           

          DROP FIELD AccountIDCheck;

            • Re: How to create a new date check status?
              Thien Dieple

              Thanks. I think I forgot to clarify that we are looking for

              if Status = '35 Day Expired', see if there were any other Status that existed before for that Account ID.

               

              Basically I want to separate the Account IDs that only had 1 '35 Day expired' status from the population by labeling them ' No Check Performed'

                • Re: How to create a new date check status?
                  Stefan Wühl

                  Maybe I am still misunderstanding your request, but I think these two lines should do what you want:

                   

                      [Account ID] as [AccountIDCheck],

                       If( Status <> '35 Day Expired', Status, If(Exists([AccountIDCheck,[Account ID]), Status, 'No Check Performed')) as Status2

                   

                  Have you tried with your data? If it doesn't work for your, could you upload some sample records and your expected result?