Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
May be try this expression in table chart for Score Card measure -
if(Count(total {<C={'A'},Check={'FALSE'}>}Check)>=1,'FALSE','TRUE')
if(C={'A'} and Check={'FALSE'},1,if(C={'B'} and Check={'FALSE'},1,0)
or as
if(Count(total {<C={'A'},Check={'FALSE'}>}Check)>=1,'FALSE',if(Count(total {<C={'B'},Check={'FALSE'}>}Check)>=1,1)
Hi,zebhashmi, I think {} brackets won't work in if() conditions, never checked though, I saw them used inside set expression mostly.
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.
I need this to be done in data load editor
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?
!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;
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 |
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