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

    Need help to create LOD

    Neeraj Goyal

      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

      1          Load1          Success

      2          Load2          Fail

      3          Load3         Success

      4          Load4         Success

      5          Load5          Fail

      5          Load5          Success

      6          Load6          Fail

      7          Load7          Fail

      7          Load7          Success

      8          Load8          success

      9          Load9          Success

      10        Load10          Fail

       

      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
          Zhouyi Zhang

          Hi, Neeraj

           

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

           

          ZZ

          • 2. Re: Need help to creat LOD
            Prayson Wilfred Daniel

            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
              Neeraj Goyal

              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
                Neeraj Goyal

                Hi Daniel ,

                 

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

                • 5. Re: Need help to creat LOD
                  Prayson Wilfred Daniel

                  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

                  3.Open the solution.twb with Notepad++

                  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:

                   

                  Job IDJob NameJob StatusUni
                  1Load1Success1
                  2Load2Fail2
                  3Load3Success3
                  4Load4Success4
                  5Load5Fail5
                  5Load5Success6
                  6Load6Fail8
                  7Load7Fail9
                  7Load7Success10
                  8Load8Success11
                  9Load9Success12
                  10Load10Fail13

                   

                  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
                    Neeraj Goyal

                    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

                    \

                    could you please help me a bit more

                     

                    • 7. Re: Need help to creat LOD
                      Zhouyi Zhang

                      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
                        Prayson Wilfred Daniel

                        Give me a moment ...

                        • 9. Re: Need help to creat LOD
                          Okechukwu Ossai

                          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
                            Neeraj Goyal

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

                            • 11. Re: Need help to creat LOD
                              Prayson Wilfred Daniel

                              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:

                                  

                              Job IDJob NameResultUni
                              1Load1READY1
                              2Load2INVALID_EXITCODE2
                              3Load3RUNNING3
                              4Load4READY4
                              5Load5INVALID_EXITCODE5
                              5Load5READY6
                              6Load6INVALID_EXITCODE8
                              7Load7INVALID_EXITCODE9
                              7Load7READY10
                              8Load8RUNNING11
                              9Load9READY12
                              10Load10INVALID_EXITCODE13
                              • 12. Re: Need help to creat LOD
                                Okechukwu Ossai

                                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
                                  Neeraj Goyal

                                  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
                                    Neeraj Goyal

                                    nope ossai , it was regarding denial solution ..

                                    1 2 Previous Next