12 Replies Latest reply: Feb 25, 2018 6:51 PM by Pablo Labbe

# Score card based on logic

I have a requirement given below. Kindly check for the rule. I need the Score as like in the given table. Up to "Check" I did in back end. And struggling to get Score. Any Idea? In back end or in front end

 Scenario 1 Rule Supplier/Customer C1 C2 Check Score if any "Check" for 1A is False, then FALSE for both 1A and 1B in Score 1A 100 100 TRUE FALSE 1A 200 200 TRUE FALSE 1A 300 250 FALSE FALSE 1B 300 300 TRUE FALSE 1B 250 250 TRUE FALSE 1B 600 600 TRUE FALSE Scenario 2 Supplier/Customer C1 C2 Check Score if all "Check" for 1A is True, then True for 1A and if any "Check " for 1B is False, then FALSE for only 1B in Score 1A 100 100 TRUE TRUE 1A 200 200 TRUE TRUE 1A 300 300 TRUE TRUE 1B 300 300 TRUE FALSE 1B 250 250 TRUE FALSE 1B 600 500 FALSE FALSE
• ###### Re: Score card based on logic

You can use this:

ps : the script does not change in both cases:

1st case :

table:

Customer,C1, C2, Check

1A,100,100,TRUE

1A,200,200,TRUE

1A,300,250,FALSE

1B,300,300,TRUE

1B,250,250,TRUE

1B,600,600,TRUE

];

left join (table)

load Customer, if( Score1A ='TRUE',if(isnull( Score2)=-1,Score1A,Score2),'FALSE') as Score ;

load Customer, if(isnull( Score1)=-1,'TRUE',Score1) as Score1A,Score2;

load * , if(FlagA=1,'FALSE') as Score1 , if(FlagB=1,'FALSE') as Score2  ;

load Customer,if(len(trim(Previous(FlagA)))=0,FlagA, Previous(FlagA)) as FlagA,FlagB  ;

load Customer, sum(Flag1A) as FlagA,sum(Flag1B) as FlagB group by Customer;

load Customer,Sum(if(Customer='1A', if(Check='FALSE',1,Peek('Flag1A')))) as Flag1A, Sum(if(Customer='1B', if(Check='FALSE',1,Peek('Flag1A')))) as Flag1B Resident table group by Customer;

Result:

2nd case :

table:

Customer,C1, C2, Check

1A,100,100,TRUE

1A,200,200,TRUE

1A,300,250,TRUE

1B,300,300,TRUE

1B,250,250,TRUE

1B,600,600,FALSE

];

left join (table)

load Customer, if( Score1A ='TRUE',if(isnull( Score2)=-1,Score1A,Score2),'FALSE') as Score ;

load Customer, if(isnull( Score1)=-1,'TRUE',Score1) as Score1A,Score2;

load * , if(FlagA=1,'FALSE') as Score1 , if(FlagB=1,'FALSE') as Score2  ;

load Customer,if(len(trim(Previous(FlagA)))=0,FlagA, Previous(FlagA)) as FlagA,FlagB  ;

load Customer, sum(Flag1A) as FlagA,sum(Flag1B) as FlagB group by Customer;

load Customer,Sum(if(Customer='1A', if(Check='FALSE',1,Peek('Flag1A')))) as Flag1A, Sum(if(Customer='1B', if(Check='FALSE',1,Peek('Flag1A')))) as Flag1B Resident table group by Customer;

Result:

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

Could you plz share the app you tried?

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

I cant; it's an app containing all of my answers in the comunity; you can copy and paste the script; and it will work.

since I'm working with inline table

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

How you are getting Score1A ,Score2 ? Since I don't have that column. Only Customer,C1, C2, Check columns I have. Score column has to be create based on Values in "Check".

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

this is called preceeding load : the load process is from bottom up:

Here's a link that can help u understand it:

And as I told u, try the code

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

I would use logic with applymap. Would make your dashboard much cleaner.

Dion

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

Is it possible to not hard-coding the value of Customer like '1A' or '1B'? Need as automated, Since we have n no. of customers.

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

well, tell me how ur source is; and we'll try to adapt it...

because, as u described it; it depends on the VALUE of the customer.

Since the check on A1 has not the same logic of the check on the B1..

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

Consider 1 as Project # and A as Customer and B as Company. Now I am comparing the Project Sub Division of 1->A vs B.

If Customer Satisfied(A-True) -Score won't affect the business even if any fault happens internally in Company(B-TRUE/FALSE) - Scenario 1(Only B will be FALSE)

If Customer not satisfied(A-FALSE) -Score will affect the Company even no faults in Company(B-TRUE) - Scenario 2

(Both A and B should be FALSE)

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

We use ApplyMap to create conditional logic from an Excel sheet.

No need to hardcode.

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

Can u share how u'll do it in this case plz?

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

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others