9 Replies Latest reply on Nov 2, 2018 1:31 PM by Kyle Kopczyk

    Help on Inserting Previous "good" value where null

    Kyle Kopczyk

      I am new to Tableau and just getting my feet wet. I need a little help on understanding where I should be looking to fix my issue. Can this be fixed in Tableau?


      I have a really large data set I am working with. I have a 'Value' that is associated with an unique ID and a timestamp on when the 'Value' happened.


      For some of the Unique ID's, this 'value' is inserted everyday (for some). The 'value' may be the same, it may change.


      I want to show the times when there is a delta change from the start 'value' to where the 'value' changes or increases. So far, I have been able to to do this, so long as the time stamp from my Unique ID's align.


      Please note the varying days on which the 'value' is accounted for. It is not always the same number of days from the previous 'value' to the next. So, using a lookup does not seem to help.

      If I add in another Unique ID with a 'value' timestamp from everyday, it changes my reference of the previous value to null and breaks the delta calculation for the Unique ID ending in 601. Where the Unique ID of 501, the calculation is still working.


      I have tried to use a ZN function or an IFNULL (and even messing with some LOOKUP functions), but inserting Zero where the values are Null creates a (in the case of Unique ID 601) a 38 followed by -38 then a bunch of zeros, then a 39 followed by a -39, and the pattern repeats.


      What I would really like to do is have Tableau to use the first known valid value and insert that for a null until it finds another non-null value and insert that new non-null value. Is this possible to accomplish in tableau? I have not used Tableau Prep at all, but would that help me? Or do I need to start creating some temp tables and managing the data from the source? What is the best way to accomplish what I am looking to do?


      I hope what I am describing makes sense. Thank you for help and guidance.

        • 1. Re: Help on Inserting Previous "good" value where null
          Joe Oppelt

          Upload a sample workbook.


          Tableau won't create rows that don't exist.  Sometimes, depending on how you have your sheet set up, we can use table calcs to force a value by using LOOKUP -1 or PREVIOUS_VALUE.  It still doesn't create new rows in the underlying table, but it can fill in a grid.

          • 2. Re: Help on Inserting Previous "good" value where null
            Kyle Kopczyk

            Alright. I had tried to use the LOOKUP -1 or Previous_Value, but there is just no consistency to the Null locations. It sounds like there is no ability to define a starting value when there is a NULL set and use that defined value until there is new non-null to reference. At least in Tableau. Did I understand that correctly?


            If my limitation is in Tableau that is alright, I can focus on my data source. I just don't know what I don't know.


            I can work on getting a sample workbook uploaded.

            • 3. Re: Help on Inserting Previous "good" value where null
              Joe Oppelt

              I'm not sure I follow the question.  That's why a workbook would help.  "See the cell in position-X?  That's what I need filled with Y-value..."


              If you have actual rows with null values, that's very easy to deal with.  If you have null cells because no row exists to fill it, that's trickier.  I can take a look as a sheet and sometimes we can force things in there.  So yes, if you can get a workbook with sample data, that will help a lot here.

              • 4. Re: Help on Inserting Previous "good" value where null
                Kyle Kopczyk

                Thanks Joe. Forgive me if this is wrong, but I did attach a sample of the workbook. I was using a live connection and believe I have extracted enough.


                To better answer your question, I think it is the latter I am trying to accomplish. I really want to fill in where data does not currently exist...and if that is even possible.

                • 5. Re: Help on Inserting Previous "good" value where null
                  Joe Oppelt

                  Kyle -- I missed the attachment earlier.


                  So I have this open.  On Sheet 4 you have a null for -10899845 in January days 1, 8, 15, and others for Current blink count.  (Actually, it looks like all non-null values are 0, so is this a worthwhile example to use?)    What are you looking to see in these cells?

                  • 6. Re: Help on Inserting Previous "good" value where null
                    Joe Oppelt

                    In the attached I have added two calcs.  INDEX just shows what Tableau sees as the index value for each cell.  This is a table calc, and if needed we can make this restart at each year or month.


                    Note:  I put dots in front of field in the text editor to get a nicer spacing of values in each cell.  They aren't decimal points.  Just a text value to force alignment.  So don't let that distract you.


                    Because this sheet has a dimensional grid index=1 exists for -10899845 for day 1 in January.

                    And Calculation1 does a LOOKUP for position zero using ZN to force a zero into null values.  Right there might be the key to what you are looking to do.

                    • 7. Re: Help on Inserting Previous "good" value where null
                      Kyle Kopczyk

                      Apologies. I'm getting used to this, not intentionally trying to waste your time.  Please see the attached and might be a better example of what I am trying to convey.


                      If you see on Sheet 4 for 10028501 - January 2 has a value of 32. January 3 and 4 have no values. The data is missing. I would like to display value = 32 for January 3 and value = 32 for January 4. Until, there is another non-missing value at a later and variable time frame. Let's say for example; 10028501 on February 16 the Value is valid and = 45. So January 3 thru February 15 Value = 32.


                      Something like this:


                      Jan 2Jan 3Jan 4...Feb 15Feb 16Feb 17Feb 18...March 1


                      I hope this makes sense...If this is outside the limits of Tableau (or even a practical work around), let me know. Again, I just don't know what I don't know.

                      • 8. Re: Help on Inserting Previous "good" value where null
                        Joe Oppelt

                        In the attached I added Calculation1.  It will run along the grid and carry numbers forward (or use the existing number if not null).  But it won't fill in dates that are not part of the grid.

                        So, for example, if you add PAOs that have additional dates (click some more in the filter) you will see that the calc will work within the dimension values that are represented by the selected set of data in the grid.  If you added the very last PAO in the list, it has a date way out in December, but the intervening dates from Jan 5 through Dec 16 do not get filled in because none of the rows in the selected data have any of those dates.


                        Probably with a robust set of data this will not be a problem, but you need to be aware of it.


                        If you want every date filled in, and if there are some dates that will not be represented in the data, then you will either need to build dummy rows in the data or build a data scaffold.  There are lots of links out the regarding data scaffolds.  It's not real pretty but it's how we address the way tableau is limited to displaying only rows and columns that actually exist.

                        1 of 1 people found this helpful
                        • 9. Re: Help on Inserting Previous "good" value where null
                          Kyle Kopczyk

                          This is a great help and pointing me in the right direction. Thank you for your help. Very much appreciated.