1 Reply Latest reply on Sep 6, 2019 6:32 PM by Raul Hoyo

    Tableu Prep 2019.2 Automatically replace nulls with first row in the Field or Field name

    Raul Hoyo

      I am working in Tableau Prep and I have it connected to an excel spreadsheet. I have a Field with a Field Name that is Date and the first row is the actual date that belongs to all the rows and null in all the other rows (Shown in Picture 1).

      I am trying to automatically fill all the rows of the Date field with the actual date which is only in the first row. I update this table with a different date in the first row everyday.

      I have tried using Group and replace and it works manually, not automatically.  It does not work because when I refresh my document it replaces the null values with the old date, not the new date.

      I have also tried pivoting when the actual date is a the field name, but then when I update my table I get an error because the date is different from the previous pivoted date.

       

      Picture 1

      Picture 2

       

       

       

      If anyone knows how to fix this, please let me know.

       

      Thank you!

      Angelica

        • 1. Re: Tableu Prep 2019.2 Automatically replace nulls with first row in the Field or Field name
          Dimitri Kourouniotis

          Use a 'self' join on a dummy field to spread the true date to the null fields like so:

           

          1. Create a dummy field called Link Field with a value of 1

           

          2. Create a separate branch with just only the date field and all the nulls removed

           

          3.

          4. You will now have a step that has only one row (your true date) and only one field.

          5. Create a Link Field in this branch as well with a value of 1

          6. Join on the Link Field. You now have the original date (non-null) spread out across every row in the original data - because you created a fake/dummy linking field to match it to.

          7. Tidy up by removing the un-needed link field and the extra date field.

          8. Make sure you rename the date field you want back to Date from Date-1

          1 of 1 people found this helpful