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

[stone1974]

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 !

[marcus_sommer · Accepted solution]

Maybe in this way:

NewTable:

load ID, date(DateIn + iterno() - 1) as Day, Value / (DateOut - DateIn) as ValuePerDay

resident [myTable] while DateIn + iterno() - 1 <= DateOut;

- Marcus

[stone1974]

Hallo Marcus,

thx very much! Was exactly what i needed!

Best regards,

Michael

[pablolabbe]

Don't forget !

When applicable please mark the appropriate replies as CORRECT https://community.qlik.com/docs/DOC-14806. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.