Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rammuthiah
Creator III
Creator III

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

13 Replies
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')

zebhashmi
Specialist
Specialist

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)

Digvijay_Singh

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

zebhashmi
Specialist
Specialist

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.

rammuthiah
Creator III
Creator III
Author

I need this to be done in data load editor

rammuthiah
Creator III
Creator III
Author

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?

Mark_Little
Luminary
Luminary

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

rammuthiah
Creator III
Creator III
Author

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

Mark_Little
Luminary
Luminary

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