12 Replies Latest reply: Feb 25, 2018 6:51 PM by Pablo Labbe RSS

    Score card based on logic

    RAM MUTHIAH M

      I have a requirement given below. Kindly check for the rule. I need the Score as like in the given table. Up to "Check" I did in back end. And struggling to get Score. Any Idea? In back end or in front end

       

      Scenario 1

      Rule

      Supplier/Customer

      C1

      C2

      Check

      Score

      if any "Check" for 1A is False, then FALSE for both 1A and 1B in Score

      1A

      100

      100

      TRUE

      FALSE

      1A

      200

      200

      TRUE

      FALSE

      1A

      300

      250

      FALSE

      FALSE

      1B

      300

      300

      TRUE

      FALSE

      1B

      250

      250

      TRUE

      FALSE

      1B

      600

      600

      TRUE

      FALSE

      Scenario 2

      Supplier/Customer

      C1

      C2

      Check

      Score

      if all "Check" for 1A is True, then True for 1A and if any "Check " for 1B is False, then FALSE for only 1B in Score

      1A

      100

      100

      TRUE

      TRUE

      1A

      200

      200

      TRUE

      TRUE

      1A

      300

      300

      TRUE

      TRUE

      1B

      300

      300

      TRUE

      FALSE

      1B

      250

      250

      TRUE

      FALSE

      1B

      600

      500

      FALSE

      FALSE

        • Re: Score card based on logic
          omar bensalem

          You can use this:

          ps : the script does not change in both cases:

           

          1st case :

          table:

          LOAD * Inline [

          Customer,C1, C2, Check

          1A,100,100,TRUE

          1A,200,200,TRUE

          1A,300,250,FALSE

          1B,300,300,TRUE

          1B,250,250,TRUE

          1B,600,600,TRUE

          ];

          left join (table)

          load Customer, if( Score1A ='TRUE',if(isnull( Score2)=-1,Score1A,Score2),'FALSE') as Score ;

          load Customer, if(isnull( Score1)=-1,'TRUE',Score1) as Score1A,Score2;

          load * , if(FlagA=1,'FALSE') as Score1 , if(FlagB=1,'FALSE') as Score2  ;

          load Customer,if(len(trim(Previous(FlagA)))=0,FlagA, Previous(FlagA)) as FlagA,FlagB  ;

          load Customer, sum(Flag1A) as FlagA,sum(Flag1B) as FlagB group by Customer;

          load Customer,Sum(if(Customer='1A', if(Check='FALSE',1,Peek('Flag1A')))) as Flag1A, Sum(if(Customer='1B', if(Check='FALSE',1,Peek('Flag1A')))) as Flag1B Resident table group by Customer;


          Result:

          Capture.PNG

          2nd case :

          table:

          LOAD * Inline [

          Customer,C1, C2, Check

          1A,100,100,TRUE

          1A,200,200,TRUE

          1A,300,250,TRUE

          1B,300,300,TRUE

          1B,250,250,TRUE

          1B,600,600,FALSE

          ];

           

           

          left join (table)

          load Customer, if( Score1A ='TRUE',if(isnull( Score2)=-1,Score1A,Score2),'FALSE') as Score ;

          load Customer, if(isnull( Score1)=-1,'TRUE',Score1) as Score1A,Score2;

          load * , if(FlagA=1,'FALSE') as Score1 , if(FlagB=1,'FALSE') as Score2  ;

          load Customer,if(len(trim(Previous(FlagA)))=0,FlagA, Previous(FlagA)) as FlagA,FlagB  ;

          load Customer, sum(Flag1A) as FlagA,sum(Flag1B) as FlagB group by Customer;

          load Customer,Sum(if(Customer='1A', if(Check='FALSE',1,Peek('Flag1A')))) as Flag1A, Sum(if(Customer='1B', if(Check='FALSE',1,Peek('Flag1A')))) as Flag1B Resident table group by Customer;


          Result:

          Capture.PNG

          • Re: Score card based on logic
            Pablo Labbe

            When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others