Score card based on logic

[rammuthiah]

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

[OmarBenSalem]

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:

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:

[rammuthiah]

Could you plz share the app you tried?

[OmarBenSalem]

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]

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

[dionverbeke]

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

Dion

[OmarBenSalem]

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

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

Preceding Load

And as I told u, try the code

[rammuthiah]

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]

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

[dionverbeke]

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

No need to hardcode.