6 Replies Latest reply: Jul 19, 2017 6:47 PM by Arnaldo Sandoval

# Calculating  row level flag  on a data set based on criteria

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'

• ###### Re: Calculating  row level flag  on a data set based on criteria

Hi,

May be as variant

Table1:

Pick(Match(Process, 'sprlit', 'route', 'send'), 1, 3, 5)*If(Status='error', 0, 1) as ValueProcess;

[Care ID, Process, Status

123, split, error

123, route, success

123, send, success

456, split, error

456, route, success

456, send, error

];

Left Join

[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

NoConcatenate

Table2:

If(SumValueID>=8 And Process='split', 1, If(SumValueID<8 And Process='split',0)) as Flag

Resident Table1;

DROP Table Table1;

Result

Regards,

Andrey

• ###### Re: Calculating  row level flag  on a data set based on criteria

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)

• ###### Re: Calculating  row level flag  on a data set based on criteria

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.

• ###### Re: Calculating  row level flag  on a data set based on criteria

Hi Aniruddhya,

There are several ways to resolve your issue, this is one of them, within the load-script:

• We load your data, with a preceding load setting a Work_Error column to 1 when the Status is error.

Table:

if(Status = 'error', 1, 0) as Work_Error;

[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

];

• We now create a mapping table with the total number of errors (sum(work_error)) for all the non-split rows.

Work:

Mapping

SUM(Work_Error) as Errs

Resident Table

Where [Process] <> 'split'

Group By [Core_ID];

• Finally the Result table is done with the code below:

Result:

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:

• ###### Re: Calculating  row level flag  on a data set based on criteria

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

• ###### Re: Calculating  row level flag  on a data set based on criteria

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