3 Replies Latest reply: Jul 20, 2018 7:35 AM by Pablo Labbe RSS

    Create new table out of table in load editor with additional rows

    Michael Steiner

      Hi!

      I would like to create a new table out of my table, but with new rows between the time span from the in and out date, and the value should be partitioned. Eg.

      One row in MyTable: ID= 2, DateIn = 15.07.2018, DateOut = 17.07.2018, Value = 6, _StoragePeriod = 3

      Rows in NewTable:

      ID= 2, DateIn = 15.07.2018, DateOut = 17.07.2018, Value = 6, ValuePerDay = 2, Day = 15.07.2018

      ID= 2, DateIn = 15.07.2018, DateOut = 17.07.2018, Value = 6, ValuePerDay = 2, Day = 16.07.2018

      ID= 2, DateIn = 15.07.2018, DateOut = 17.07.2018, Value = 6, ValuePerDay = 2, Day = 17.07.2018

       

      [myTable]:

      Load*,

                   If([_InStock] = '1', Date(Floor(Now()))-[DateIn],[DateOut]-[DateIn])+1 As [_StoragePeriod];

      LOAD

      ID,

      DateIn,

      DateOut,

      Value;

      SELECT

                   ID,

      DateIn,

      DateOut,

      Value,

      if(IsNull([DateOut]), 1, 0) AS [_InStock],

      FROM myTable;



      Any help is appreciated.

      Thx !