Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rammuthiah
Contributor II

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

Tags (1)
13 Replies
Digvijay_Singh
Honored Contributor III

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

zebhashmi
Valued Contributor

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)

Digvijay_Singh
Honored Contributor III

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.

zebhashmi
Valued Contributor

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.

rammuthiah
Contributor II

Re: Score based on logic

I need this to be done in data load editor

rammuthiah
Contributor II

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?

Partner
Partner

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;

rammuthiah
Contributor II

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

Partner
Partner

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