
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Create new table out of table in load editor with additional rows
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 !
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hallo Marcus,
thx very much! Was exactly what i needed!
Best regards,
Michael

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
