Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a following requirement
I have core ids where the process flow is like split-->route--->send.So first it will come at split ,then route and then sent.It can fail at any of the stages.Example data set like below
Now I want a calculate a flag field against core ids when it fails exclusively at 'split'. I will not consider the core id if it also fails at other stages apart from 'split'.An example below is like.I have flagged core id 456 as 0 since it fails at 'send' , apart from 'split'
Hi,
May be as variant
//A broader solution of the task
Table1:
LOAD*,
Pick(Match(Process, 'sprlit', 'route', 'send'), 1, 3, 5)*If(Status='error', 0, 1) as ValueProcess;
LOAD*Inline
[Care ID, Process, Status
123, split, error
123, route, success
123, send, success
456, split, error
456, route, success
456, send, error
];
Left Join
LOAD
[Care ID],
Sum(ValueProcess) as SumValueID
Resident Table1
Group By [Care ID];
//Value SumValueID indicates state[Care ID] !!!
//split, error
//route, error - SumValueID =0
//send, error
//split, success
//route, error - SumValueID =1
//send, error
//split, error
//route, success - SumValueID =3
//send, error
//split, error
//route, error - SumValueID =5
//send, success
//split, success
//route, success - SumValueID =4
//send, error
//split, error
//route, success - SumValueID =8
//send, success
//split, success
//route, error - SumValueID =6
//send, success
//split, success
//route, success - SumValueID =9
//send, success
//If you solve your task
NoConcatenate
Table2:
LOAD*,
If(SumValueID>=8 And Process='split', 1, If(SumValueID<8 And Process='split',0)) as Flag
Resident Table1;
DROP Table Table1;
Result
Regards,
Andrey
Thanks a lot Andrey for taking out time to reply..
can you let me know if there is any special reason for using the numbers as 1,3 ,5 instead of 1,2,3 in the formula Pick(Match(Process, 'sprlit', 'route', 'send'), 1, 3, 5)
Hi Aniruddhya,
There are several ways to resolve your issue, this is one of them, within the load-script:
Table:
Load *,
if(Status = 'error', 1, 0) as Work_Error;
LOAD * Inline
[Core_ID, Process, Status
123, split, error
123, route, success
123, send, success
456, split, error
456, route, success
456, send, error
555, split, error
555, route, error
555, send, error
];
Work:
Mapping
Load [Core_ID],
SUM(Work_Error) as Errs
Resident Table
Where [Process] <> 'split'
Group By [Core_ID];
Result:
Load [Core_ID],
Process,
Status,
// Work_Flag,
if(Process = 'split', Work_Error * ( 1 - Sign(ApplyMap('Work', [Core_ID], 0))), 0) as Flag
Resident Table;
The Flag column will be zero for any row having a process different to 'split'.
If the process is split, we use the ApplyMap function to retrieve the total numbers of error for that [Core_ID], based on the sample data, it could be 0, 1 or 2; zero means that neither 'route' nor 'send' had errors, 1 or 2 indicate 'route' or 'send' or both have errors.
The SIGN of zero is always zero, and if 'split' had errors the result will be = 1 * ( 1 - 0 )
If 'route' or 'send' had errors SIGN() will be always 1, forcing Flag to be zero, regardless of the status of the split process.
The result is shown below:
Thanks a ton...
I looked into the solution and it requires me to do a group by on the data set..but since my model is already developed(a many other KPIs are on it ) is there a way we can do this in the front end calculation only without touching the data set using some aggr functionality....
No special reasons. Three simple number of in terms of mathematics. The amount has a unique value, by which you can accurately determine the state of the process at all stages.
Aniruddhya,
If we look at my suggestion, I am doing a GROUP BY while creating a mapping table, mapping tables are dropped at the end of the script, and the Work_Error (or Work_Flag) exist inside the script only.
HTH