13 Replies Latest reply: Mar 15, 2018 8:51 AM by RAM MUTHIAH M RSS

    Score based on logic

    RAM MUTHIAH M

      Hi

       

      Problem listed below. I need the score to be False for A if anyone in A is False. But for B, if any false in B and if any false in A, then false, if all are True in A and any of B is false, then B should be true.

       

      C

      C1

      C2

      Check

      Score

      A

      100

      100

      TRUE

      FALSE

      A

      200

      200

      TRUE

      A

      300

      250

      FALSE

      B

      300

      300

      TRUE

      FALSE

      B

      250

      250

      TRUE

      B

      600

      600

      TRUE

      Score for A

      if any false in A, then False

      Score for B

      if any false in B and if all A is true, then True, Otherwise False

        • Re: Score based on logic
          Digvijay Singh

          May be try this expression in table chart for Score Card measure -

           

          if(Count(total {<C={'A'},Check={'FALSE'}>}Check)>=1,'FALSE','TRUE')

          • Re: Score based on logic
            Jahanzeb Hashmi

            if(C={'A'} and Check={'FALSE'},1,if(C={'B'} and Check={'FALSE'},1,0)

            or as

            Digvijay Singh

            if(Count(total {<C={'A'},Check={'FALSE'}>}Check)>=1,'FALSE',if(Count(total {<C={'B'},Check={'FALSE'}>}Check)>=1,1)

            • Re: Score based on logic
              RAM MUTHIAH M

              I need this to be done in data load editor

              • Re: Score based on logic
                RAM MUTHIAH M

                But here the problem is A is coming from 1 table and B is coming from another table. Check is based on if condition.Is it possible to use based on Check for bith A and B Value?

                • Re: Score based on logic
                  Mark Little

                  !Hi,

                   

                  Depends how the values are calculate in C1 and C2? Are the just data values or they a sum?

                   

                  In script you will be able to use Peek or previous to do this.

                  Assuming that the data is held as in that table shown. you will need something like the below..

                   

                  DATA:

                  Load * Inline [

                  C, C1, C2, Check,

                  A, 100, 100, TRUE

                  A, 200, 200, TRUE

                  A, 300, 250, FALSE

                  B, 300, 300, TRUE

                  B, 250, 250, TRUE

                  B, 600, 600, TRUE];

                   

                   

                  NoConcatenate

                   

                   

                  NewTable:

                  LOAD

                  C,

                      C1,

                      C2,

                      Check,

                      IF(ROWNO()=1,

                      IF(Check = 'FALSE',

                      1,

                              0

                            ),

                          PEEK(FalseCount,-1) + IF(Check = 'FALSE',1,0)

                        ) AS FalseCount    

                  Resident DATA;

                   

                   

                  DROP Table DATA;

                   

                   

                  LEFT JOIN

                   

                   

                  LOAD

                  C,

                      IF(SUM(FalseCount) > 0, 'FALSE','TRUE') AS SCORE

                  Resident NewTable

                  GROUP BY C;

                    • Re: Score based on logic
                      RAM MUTHIAH M

                      Hello Mark,

                       

                      Thanks for you time.

                      Here I fetch A and B from a separate table and use if condition for check if isnull(Check1)->Check2 or else Check 1 as Check. After finding difficulty to proceed for Score.

                       

                      DATA_1:

                      Load * Inline [

                      Supplier, C1, C2,

                      A, 100, 100,

                      A, 200, 200,

                      A, 300, 250,

                       

                      ];

                       

                      DATA_2:

                      Customer, C1, C2,

                      Load * Inline [

                      B, 300, 300,

                      B, 250, 250,

                      B, 600, 600,

                       

                      ];

                       

                      Now I am comparing the value of Customer with Supplier based on C1 and C2 in both Tables.

                      Supplier/Customer

                      Scenario 1

                       

                      Supplier/Customer

                      C1

                      C2

                      Check1/Check2

                      Score

                      A

                      100

                      100

                      TRUE

                      FALSE

                      A

                      200

                      200

                      TRUE

                      FALSE

                      A

                      300

                      250

                      FALSE

                      FALSE

                      B

                      300

                      300

                      TRUE

                      FALSE

                      B

                      250

                      250

                      TRUE

                      FALSE

                      B

                      600

                      600

                      TRUE

                      FALSE

                      Scenario 2

                      Supplier/Customer

                      C1

                      C2

                      Check1/Check2

                      Score

                      A

                      100

                      100

                      TRUE

                      TRUE

                      A

                      200

                      200

                      TRUE

                      TRUE

                      A

                      300

                      300

                      TRUE

                      TRUE

                      B

                      300

                      300

                      TRUE

                      FALSE

                      B

                      250

                      250

                      TRUE

                      FALSE

                      B

                      600

                      500

                      FALSE

                      FALSE

                        • Re: Score based on logic
                          Mark Little

                          HI Ram,

                           

                          Just to confirm, are we saying that in Scenario 1 we get the Correct results. But incorrect for Scenario 2, score should be false?

                           

                          Mark

                            • Re: Score based on logic
                              Mark Little

                              If so,

                               

                              DATA:

                              Load * Inline [

                              C, C1, C2, Check,

                              A, 100, 100, TRUE

                              A, 200, 200, TRUE

                              A, 300, 300, TRUE

                              B, 300, 300, TRUE

                              B, 250, 250, TRUE

                              B, 600, 500, FALSE];

                               

                               

                              NoConcatenate

                               

                               

                              NewTable:

                              LOAD

                              1 As Group,

                              C,

                                  C1,

                                  C2,

                                  Check,

                                  IF(ROWNO()=1,

                                  IF(Check = 'FALSE',

                                  1,

                                          0

                                         ),

                                      PEEK(FalseCount,-1) + IF(Check = 'FALSE',1,0)

                                    ) AS FalseCount     

                              Resident DATA

                              ORDER by C desc;

                               

                               

                              DROP Table DATA;

                               

                               

                              LEFT JOIN

                               

                               

                              LOAD

                              Group,

                                  IF(SUM(FalseCount) > 0, 'FALSE','TRUE') AS SCORE

                              Resident NewTable

                              GROUP BY Group;

                               

                              We add a new column to group our complete combined table.

                               

                              Mark

                              • Re: Score based on logic
                                RAM MUTHIAH M

                                Your logic is correct. But here I am fetching value for A and B from 2 tables. And using check 1 for A and Check 2 for B to perform the logic.

                                 

                                Supplier/Customer

                                C1

                                C2

                                Check 1

                                Check 2

                                Check(if isnull(Check1),Check 2,Check 1))

                                A

                                100

                                100

                                TRUE

                                 

                                TRUE

                                A

                                200

                                200

                                TRUE

                                 

                                TRUE

                                A

                                300

                                250

                                FALSE

                                 

                                FALSE

                                B

                                300

                                300

                                 

                                TRUE

                                TRUE

                                B

                                250

                                250

                                 

                                TRUE

                                TRUE

                                B

                                600

                                600

                                 

                                TRUE

                                TRUE

                                 

                                 

                                So, I am confusing how to perform the logic in front end expression

                            • Re: Score based on logic
                              RAM MUTHIAH M

                              Hello Mark,

                               

                              Is there any way to do the logic mentioned in the given link? Logic to Find Value for Score1 and Score 2