6 Replies Latest reply on Jun 13, 2018 1:46 PM by Gerardo Varela

    Missing numbers between 2 columns

    Nand Reddy

      Hi All


      Need help identifying missing numbers between 2 columns -- Start_Ru and End_Ru


      Column 1 missing values - 1 - 26

      Column 2 is fine

      Column 3 missing values -- 28,29



      Tried with index/Rank but no success





        • 1. Re: Missing numbers between 2 columns
          Joe Oppelt

          I don't understand what constitutes "missing".  You refer to three columns, but you only have two columns here.


          I have 10.1 and I have your workbook open.  Help me understand what you need to do.

          • 2. Re: Missing numbers between 2 columns
            Nand Reddy

            Thanks Joe for reply


            For Label "T11" and Position "T" I have occupied -- Start Rack_unit (Start_Ru) and End Rack_unit (End_Ru)


            I need to created computed item to show Un-occupied Rack Units i,e ( 1 - 26 ,28,29, 34 & 38 )


            • 3. Re: Missing numbers between 2 columns
              Joe Oppelt

              Tableau isn't going to create cells for data that doesn't exist.


              "Finding" the missing numbers is one thing.  We can probably do something with that.  Creating those cells is something completely different.

              I'm curious about your original question wherein you said "Column 2 is fine".  Do you have a column that has all the numbers in your actual data?  If so, we might be able to leverage that.

              • 4. Re: Missing numbers between 2 columns
                Nand Reddy

                Not necessarily we have to create cells to show the missing sequence.

                we just need to show the unoccupied racks from start_ru and end_ru  which are (( 1 - 26 ,28,29, 34 & 38 ) - this data is not stored

                • 5. Re: Missing numbers between 2 columns
                  Joe Oppelt

                  In the attached, Sheet 2 displays a series of table calcs running along ONE of your  columns.  (I chose Start Ru.)


                  Note that I changed many of your columns to dimensions.  Even though they are numeric, you want to run down the table in the dimensional order of that column.


                  For kicks I threw in an index column.  In the end, we don't really need it.  I made a calc that pulls the previous value along the column using LOOKUP -1.


                  But the real calc you need to use is something like the [Continuous...] calc.  What I'm doing here is comparing the current value with the prior.  If the prior is not one less than the current, there is a gap.  (And the difference between the previous and the current tells you all the missing values, which I did not list out, but you can derive.)


                  So if the value of the [Continuous...] calc is 1, you have found a gap.


                  But you want to find gaps in the combined values of the Start and End columns.


                  On Sheet 3 I made a copy of your data source and did PIVOT on the two columns.  Show data on this data source.  What this does is create multiple rows where there is both an END and a START value for a given rack.  PIVOT, by default, makes two columns:  [Pivot Field Names] and [Pivot Field Values].  I have displayed them on sheet 3.  That's all I did with the first copy of your data source.


                  On Sheet 4 I made a second copy of your data source, only this time I renamed those two new columns.  (Just to demonstrate that you can do this.)  Note also that in both copy-1 and copy-2 of your data source, I made both columns dimensions.


                  On Sheet 5 is the real work.  I rebuilt the [Continuous...] calc here to use [rack#]. Now you can see where there are gaps again -- this time with the combined values of Start- and End-Ru.


                  Now you can see that on row 3, we have a value of 1, and therefore the value in row 3 represents a gap from row 2, and [Previous] says that the previous value was zero, meaning you are missing everything from zero to the value in row 3 (27), thus you are missing 1-through-26.  Etc.

                  • 6. Re: Missing numbers between 2 columns
                    Gerardo Varela

                    Hi All,

                      Here is alternative version but it isn't quite there. I haven't figured out how to remove the first bin, though.  I started by pivoting his data and making bins of the new values. Then a lookup filter to keep 0 values.