6 Replies Latest reply: Jul 19, 2017 6:47 PM by Arnaldo Sandoval RSS

    Calculating  row level flag  on a data set based on criteria

    Aniruddhya Dutta

      Hi All,

      I have a following  requirement

      I have core ids  where the  process flow is like split-->route--->send.So first it will come at split ,then route and then sent.It can fail at any of the stages.Example data set like below

      data set.JPG

       

      Now I want a calculate a flag field  against core ids when it fails exclusively at 'split'. I will not consider the core id if it also fails at other stages apart from 'split'.An example below is like.I have flagged core id 456 as 0 since it fails at 'send' , apart from 'split'

       

      data set.JPG

        • Re: Calculating  row level flag  on a data set based on criteria
          Andrey Khoronenko

          Hi,

           

          May be as variant

           

          //A broader solution of the task

           

          Table1:

          LOAD*,

          Pick(Match(Process, 'sprlit', 'route', 'send'), 1, 3, 5)*If(Status='error', 0, 1) as ValueProcess;

          LOAD*Inline

          [Care ID, Process, Status

          123, split, error

          123, route, success

          123, send, success

          456, split, error

          456, route, success

          456, send, error

          ];

           

          Left Join

          LOAD

          [Care ID],

          Sum(ValueProcess) as SumValueID

          Resident Table1

          Group By [Care ID];

           

          //Value SumValueID  indicates state[Care ID] !!!

           

          //split, error

          //route, error   - SumValueID =0  

          //send, error

           

           

          //split, success

          //route, error   - SumValueID =1  

          //send, error

           

           

          //split, error

          //route, success   - SumValueID =3  

          //send, error

           

           

          //split, error

          //route, error   - SumValueID =5  

          //send, success

           

           

          //split, success

          //route, success   - SumValueID =4  

          //send, error

           

           

          //split, error

          //route, success   - SumValueID =8  

          //send, success

           

           

          //split, success

          //route, error   - SumValueID =6  

          //send, success

           

           

          //split, success

          //route, success   - SumValueID =9  

          //send, success

           

           

          //If you solve your task

           

          NoConcatenate

          Table2:

          LOAD*,

          If(SumValueID>=8 And Process='split', 1, If(SumValueID<8 And Process='split',0)) as Flag

          Resident Table1;

           

          DROP Table Table1;

           

          Result

          1.jpg

          Regards,

          Andrey

          • Re: Calculating  row level flag  on a data set based on criteria
            Arnaldo Sandoval

            Hi Aniruddhya,

            There are several ways to resolve your issue, this is one of them, within the load-script:

             

            • We load your data, with a preceding load setting a Work_Error column to 1 when the Status is error.

            Table:

            Load *,

                if(Status = 'error', 1, 0) as Work_Error;

              

            LOAD * Inline

            [Core_ID, Process, Status

            123, split, error

            123, route, success

            123, send, success

            456, split, error

            456, route, success

            456, send, error

            555, split, error

            555, route, error

            555, send, error

            ];

             

            • We now create a mapping table with the total number of errors (sum(work_error)) for all the non-split rows.

            Work:

            Mapping

            Load [Core_ID],

                 SUM(Work_Error) as Errs

            Resident Table

            Where [Process] <> 'split'

            Group By [Core_ID];

             

            • Finally the Result table is done with the code below:

            Result:

            Load [Core_ID],

                 Process,

                 Status,

            //   Work_Flag,

                 if(Process = 'split', Work_Error * ( 1 - Sign(ApplyMap('Work', [Core_ID], 0))), 0) as Flag

            Resident Table;

             

            The Flag column will be zero for any row having a process different to 'split'.

             

            If the process is split, we use the ApplyMap function to retrieve the total numbers of error for that [Core_ID], based on the sample data, it could be 0, 1 or 2; zero means that neither 'route' nor 'send' had errors, 1 or 2 indicate 'route' or 'send' or both have errors.

             

            The SIGN of zero is always zero, and if 'split' had errors the result will be = 1 * ( 1 - 0 )

             

            If 'route' or 'send' had errors SIGN() will be always 1, forcing Flag to be zero, regardless of the status of the split process.

             

            The result is shown below:

             

            Flag.png