-
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
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:
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
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
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:
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
I have done upto Check, but while tried for FalseCount, the row count is getting increased based on your 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
-