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

# Score based on logic

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

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

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

Hi,zebhashmi, I think {} brackets won't work in if() conditions, never checked though, I saw them used inside set expression mostly.

• ###### Re: Score based on logic

You are right. I was thinking it's not going to work so that's why I added the second condition in your recommendation.

Thank You again.

• ###### Re: Score based on logic

I need this to be done in data load editor

• ###### Re: Score based on logic

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

!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:

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:

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

C,

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

Resident NewTable

GROUP BY C;

• ###### Re: Score based on logic

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:

Supplier, C1, C2,

A, 100, 100,

A, 200, 200,

A, 300, 250,

];

DATA_2:

Customer, C1, C2,

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

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

If so,

DATA:

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:

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

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

I have done upto Check, but while tried for FalseCount, the row count is getting increased based on your logic.

Score card based on logic

Can you lookup the requirement?

• ###### Re: Score based on logic

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

Hello Mark,

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