Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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:
Could you plz share the app you tried?
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
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".
I would use logic with applymap. Would make your dashboard much cleaner.
Dion
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
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.
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..
We use ApplyMap to create conditional logic from an Excel sheet.
No need to hardcode.