7 Replies Latest reply on Aug 24, 2018 9:15 AM by Aditya Chavan

    Creating Null rows if record unavailable for that month and replacing them with previous month Data (Padding)

    Aditya Chavan

      Hi folks,

       

      I have data which looks like this-

       

      TypeDateVal 1Val 2Val 3
      A1/1/20180.90.80.95
      B1/10/20180.80.80.9
      C1/12/201810.90.95
      D1/1/20180.650.80.9
      E1/1/20180.850.80.9
      A2/3/201810.80.95
      C2/13/20180.750.80.9
      D2/3/201810.90.95
      E2/3/20180.80.750.95
      B4/24/20180.750.80.9
      C4/24/201810.90.95

       

      I'm creating a usual time-line graph for a main metric which is weighted sum of values of Type A through E. As you can see, here I don't have records for some of the Types in every month. This is affecting the weighted sum of my main metric, which is causing my graph to drop every time there are less records. What I would like is something like this-

        

       

      TypeDateVal 1Val 2Val 3
      A1/1/20180.90.80.95
      B1/10/20180.80.80.9
      C1/12/201810.90.95
      D1/1/20180.650.80.9
      E1/1/20180.850.80.9
      A2/3/201810.80.95
      B2/13/20180.80.80.9
      C2/13/20180.750.80.9
      D2/3/201810.90.95
      E2/3/20180.80.750.95
      A3/5/201810.80.95
      B3/14/20180.80.80.9
      C3/15/20180.750.80.9
      D3/16/201810.90.95
      E3/17/20180.80.750.95
      A4/14/201810.80.95
      B4/24/20180.750.80.9
      C4/24/201810.850.95
      D4/25/201810.90.95
      E4/25/20180.80.750.95

      .......all the way upto current month, whichever that might be.

       

      I have to have a row for each Type in every month of the year. Day is not important. If for a month there is no record generated for type say A, then it should refer to previous month's value of type A.

      I'm also open to any other method which would give me carry forwarded data for each Type if no record exist and prevent my graphs from dropping. Unfortunately, I cannot share the data or twbx, which I know would have made it lot easier.

      The data for each Type comes from different Excel worksheets and I'm using union all to bring them together.

       

      Thank you.