Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
stone1974
Contributor
Contributor

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 !

1 Solution

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

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

View solution in original post

3 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

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
Contributor
Contributor
Author

Hallo Marcus,

thx very much! Was exactly what i needed!

Best regards,

Michael

pablolabbe
Luminary Alumni
Luminary Alumni

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.