2 Replies Latest reply on Nov 12, 2018 6:56 AM by Alexander Mik

    Find every year in From To date field

    Alexander Mik
    Alexander Mik Nov 12, 2018 5:25 AM

      Hi all,

       

      I have a data set which looks like this:

       

      Product          Price          Valid from          Valid to

      A                        10            1-3-2014          1-4-2016  

      A                        12            2-4-2016           12-3-2018

      A                        13            13-3-2018          -

       

      What I need is this:

       

      Product          Price          Year

      A                    10               2014

      A                    10               2015

      A                    12               2016

      A                    12               2017

      A                    13               2018

       

      How can I achieve this? I already tried it with prep but can't get the answer. Hope you guys can help me.

       

      Best regards,

      Paul

      • 65 Views
      • Categories: Calculations, Data Sources & Connections, Extracts, Formatting & Display
      • Tags:
        • 1. Re: Find every year in From To date field
          Ankit Bansal
          Ankit Bansal Nov 12, 2018 5:42 AM (in response to Alexander Mik)

          Alex,

           

          You can simply create a table with all the years listed like:

           

          year_column

          2000

          2001

          ...

          ..

          2099

           

          Then join your original dataset with this table with below condition

           

          year_column >=year(valid from) and  year_column <=ifnull(year(valid To),year(valid from))

           

          You can use tableau's create join calculation option to derive the right hand side columns in above expression.

           

          Also if your data source in not a database but its a file then you will not be able to do >= and <= joins. In that case just join 2 dataset on 1=1 and then create a calculation column as above expression, put it on filter shelf and choose TRUE.

           

          Hope it helps.

           

          Thanks,

          AB

          • 2. Re: Find every year in From To date field
            Alexander Mik
            Alexander Mik Nov 12, 2018 6:56 AM (in response to Ankit Bansal)

            Great that worked! I justed the 1=1 method in Tableau Prep