Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
aniruddhyadutta
Creator
Creator

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

data set.JPG

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'

data set.JPG

6 Replies
ahaahaaha
Partner - Master
Partner - Master

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

1.jpg

Regards,

Andrey

aniruddhyadutta
Creator
Creator
Author

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)

ArnadoSandoval
Specialist II
Specialist II

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:

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

];

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

Work:

Mapping

Load [Core_ID],

     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:

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:

Flag.png

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.
aniruddhyadutta
Creator
Creator
Author

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

ahaahaaha
Partner - Master
Partner - Master

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.

ArnadoSandoval
Specialist II
Specialist II

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

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.