Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rammuthiah
Contributor II

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

12 Replies
OmarBenSalem
Esteemed Contributor

Re: Score card based on logic

You can use this:

ps : the script does not change in both cases:

1st case :

table:

LOAD * Inline [

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:

Capture.PNG

2nd case :

table:

LOAD * Inline [

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:

Capture.PNG

rammuthiah
Contributor II

Re: Score card based on logic

Could you plz share the app you tried?

OmarBenSalem
Esteemed Contributor

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

rammuthiah
Contributor II

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".

dverbeke
Contributor III

Re: Score card based on logic

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

Dion

OmarBenSalem
Esteemed Contributor

Re: Score card based on logic

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

Capture.PNG

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

Preceding Load

And as I told u, try the code

rammuthiah
Contributor II

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.

OmarBenSalem
Esteemed Contributor

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..

dverbeke
Contributor III

Re: Score card based on logic

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

No need to hardcode.

Community Browser