Get latest count of data

[User1999]

 I need help in solving this problem. Each process would have many jobs in it and I need to show the failure count when a job is failed. 

for failure we should consider two columns 1. Job_run_status = Failed and Obj_run_status = F so this case the count is 1

When someone rerun the job and if it is successful a new entry would be created for the same data where the Job_run_status = finished and Obj_run_status = S, you can see it in the next entry after the highlighted one. 

since this job has ran successfully my failure count should change to 0 . 

how do i proceed next ? There will many process id and many jobs in project I'm working

[User1999]

when I was trying it for smaller data set it is working fine. But when I work for a larger it is not working. 

[User1999]

Try with this dataset, 
https://docs.google.com/spreadsheets/d/1bOCOihHKfruVraeElaKoNgP8WQAE7gwf/edit?usp=sharing&ouid=10521...

for job_end_timestamp = 25/01/2024 and job_name = JOB_ingest_GSColumn_transform_view , target_table_name = "glbl_r_sapplm_glbl_plm_wui_obj_ecr.t_plmi_ecr_crt_at_glbl" you can find the records where after rerun for same target table there would be 2 entries. One with Job_run_status_cd = Failed and Job_run_status_cd = Finished.

[Anonymous]

Hi @User1999 

What does it return when working for the larger one?

Could you please show me some screenshots or share your data(without sensitive data), so we can help you better.

^{Best Regards,}

^{Zhengdong Xu}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[User1999]

There is no sensitive data, are you able to open that link ? One more condition is that in some conditions the job_run_status = failed but object_run_status = S , so this considered as finished only

[User1999]

In the screenshot object_run_status = S , the outcome should be finished but it is showing failed. I'm not sure whether the earlier function on job_end_timestamp is working correct or not. You check with the large dataset I have shared ?

[Anonymous]

Hi @User1999 

Please try this:

First of all, I made some changes based on your data for better testing:

Then create a calculate column to return the final result:

OUTCOME = 
VAR _previousStatus = 
        CALCULATE(
            SELECTEDVALUE('Table (2)'[job_run_status_cd]),
            FILTER(
                ALLSELECTED('Table (2)'),
                'Table (2)'[process_id]=EARLIER('Table (2)'[process_id])
                &&
                'Table (2)'[job_name]=EARLIER('Table (2)'[job_name])
                &&
                'Table (2)'[job_end_timwstamp]>EARLIER('Table (2)'[job_end_timwstamp])
                )
            )
RETURN IF(
        'Table (2)'[job_run_status_cd]="Failed"
        &&
        'Table (2)'[object_run_status]="F"
        &&
        _previousStatus="Finish",
            "Finish",
            'Table (2)'[job_run_status_cd]
        )

Then create another calculate column to countrow:

FAILNUM = IF(
            'Table (2)'[OUTCOME]="Failed",
                CALCULATE(
                    COUNTROWS('Table (2)'),
                    FILTER(
                        ALLSELECTED('Table (2)'),
                        'Table (2)'[OUTCOME]="Failed"
                    )
                )
            )

The count of failed process will appear in the row of the failed process.

The result is as follow:

^{Best Regards,}

^{Zhengdong Xu}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}