1 2 Previous Next 21 Replies Latest reply on Apr 27, 2017 3:25 AM by Neeraj Goyal

# Need help to create LOD

Dear all ,

I am facing  one problem.. please see the below data to get the idea about problem .

Tables contains data like

Job ID  Job Name  Job Status

Here now I have to show the count of Fail job .. but caution !! before you say the count is -5 by using the calculation countD( if job status="Fail" then job id)  then please note here .. job 5 first fail then turned into success ( may be somebody re run the job from backend and its turn into success). So it is assume job 5 and 7 have been success. Now I have to consider these jobs as success..here the point is I have to show the actual fail count of job which is - 3 for the job id 2,6,10 .I guess It can be achieved through LOD ..but how ?please let  me know .

• ###### 1. Re: Need help to creat LOD

Hi, Neeraj

Please find attache workbook as my solution. Hope this could help

ZZ

• ###### 2. Re: Need help to creat LOD

Assuming that the logic is alway from Fail to Success, this can be done this way:

Since F in Fail comes prior to S in Success, alphabetically, then MAX will always return the largest letter of the alphabet. In this case, if we have MAX(Fail, Fail ) = Fail, MAX(Fail, Success) = Success, MAX(Fail, Success, Fail) = Success

FindMax:=

{ FIXED [Job ID] :MAX([Job Status])}

To Count Failed :=

{FIXED [Job Status]: SUM(IIF([FindMax]='Fail',1,0))}

NB: This will not work if the logic e.g. of Job ID 5 goes from Fail to Success then back to Fail. Since MAX returns the largest letter of the alphabet.

You have to be very careful when using MIN and MAX . Knowing how they behave in all give contexts is very important

• ###### 3. Re: Need help to creat LOD

Hi zhang,

I am using v 9.3 so could you please share the work book  having v 9.3 or below.

• ###### 4. Re: Need help to creat LOD

Hi Daniel ,

could you please share the workbook having v 9.3 or below.

• ###### 5. Re: Need help to creat LOD

Sadly I have version 10.01 and 10.2.

But you can:

1. change the solution.twbx to solution.zip

2. Unzip the file, to get solution.twb

4. Press Ctrl+H to get Find & Replace

5. Replace 10.0.1 to 9.3

6. Save the file, and it should open without a problem.

If you cannot. This is basically the Count Fail expression:

{FIXED [Job Status]: SUM(IIF({ FIXED [Job ID] :MAX([Job Status])}='Fail',1,0))}

Data Source:

Breakdown:

{ FIXED [Job ID] :MAX([Job Status])} // Fixed at Job ID level of detail, find the largest word alphabetical

SUM(IIF({ FIXED [Job ID] :MAX([Job Status])}='Fail',1,0))  // Fixed at Job ID level of detail, if the largest word alphabetical is Fail then 1

{FIXED [Job Status]: SUM(IIF({ FIXED [Job ID] :MAX([Job Status])}='Fail',1,0))}//Fixed at Job Status, give me the count of all fail status

2 of 2 people found this helpful
• ###### 6. Re: Need help to creat LOD

Hi Daniel ,

yea I am able to open your workbook now .. But this salutation not working for me.. Let me explain situation a bit more ..

Actually in my  table I have a column result instead of job status ..so the result column contain either "READY" or "RUNNING"

OR "INVALID_EXITCODE".  here INVALID_EXITCODE means a job is failed (please assume above job id : 2,5,6,7,10 having status "INVALID_EXITCODE" instead of Fail ...

Now to achieve the count of only INVALID_EXITCODE .. first I am creating a calculation field ..Find max which contain the code : {Fixed [job id] : max([result])}

count fail which contain the code .. {fixed [result] : sum (iif([find max]= "INVALID_EXITCODE" ,1,0)}

but the result / count displaying - 0

expecting -5

\

• ###### 7. Re: Need help to creat LOD

Hi, Neeraj

unfortunately, I don't have 9.3. I list the steps below for your reference:

Step 1: create two calculation fields

[Calculation1]

{FIXED [Job Name]:COUNTD([Job Status])}

[Calculation2]

[Calculation1] = 1 AND [Job Status] = "Fail"

step 2: drag Job ID, Job Name, Job Status to Rows

Step 3: Drag [Calculation2] to filter and select True.

I attache snapshot for you as well.

ZZ

• ###### 8. Re: Need help to creat LOD

Give me a moment ...

• ###### 9. Re: Need help to creat LOD

Hi Neeraj,

It is possible that you may have multiple numbers of Job status per Job Name, since people are running different jobs at the backend multiple times.  In that case, you need a robust solution which will work at all times for all cases. This is what I have tried to do for you.

Bear in mind that Tableau doesn't display duplicate rows by default. So, if you have Load5 Fail recorded 3 times for Job ID 5, then Tableau will only display it once.

To get a solution which will work for all possible cases, you need an additional field to identify each row or Job Status entry. This should ideally be a Timestamp field. This will be easy if backend users record a DateTime for each time there is a job re-run. If you don't have a DateTime field, then you need to simply create a sequential number called Row ID to identify the rows. I have done this for you below.

You need this Row ID or DateTime field to identify the MAX or latest entry per Job Status.

Solution.

1. Create calculated field [Latest Backend Job Status]

{ FIXED [Job ID], [Job Name]: MAX([Row ID])}

This returns the Maximum Row ID per Job Name, which is the latest backend Job Status per Job Name.

2. Create calculated field [Failed Jobs]

IF [Row ID] = [Latest Backend Job Status] AND [Job Status] = "Fail" THEN [Job Name] END

This returns a list of all "truly" failed Job Names. When building your views, put [Failed Jobs] on the filter shelf and exclude null values.

3. Create calculated field [# of Failed Jobs]

COUNT([Failed Jobs])

This returns the count of truly failed jobs.

My solution is in version 9.3 but I'm unable to attach the workbook now. Will do so later today.

Hope this helps.

Ossai

• ###### 10. Re: Need help to creat LOD

nope ..   not working ..showing -22 ; expecting -5

• ###### 11. Re: Need help to creat LOD

This is how I will go about:

Counting INVALID_EXISTCODE without caring if it changed to READY

{FIXED Result: SUM(IIF([Result]='INVALID_EXITCODE',1,0))}

Counting INVALID_EXISTCODE with caring if it changed to READY, namely if INVALID_EXITCODE changed to READY then don't count.

{ FIXED [Result]:sum (iif({ FIXED [Job ID]:MAX([Result])}= "INVALID_EXITCODE" ,1,0))}

DATA:

• ###### 12. Re: Need help to creat LOD

Hi Neeraj,

Is this a response to the solution I posted?

nope ..   not working ..showing -22 ; expecting -5

If yes, then tell me what you did because you can't get -22.

Ossai

• ###### 13. Re: Need help to creat LOD

Hi Ossai,

Yea I wants exactly  what is you have shown in your first screen shot .. could you please share the work book having version 9.3 or below..

• ###### 14. Re: Need help to creat LOD

nope ossai , it was regarding denial solution ..

1 2 Previous Next