Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

ecolomer
Honored Contributor II

Split an file

Hello,

See if you can help me with a lock that I have.

My problem is the next:

- I have a file containing: Contract, Start Date and End Date

I would like to get a file that has:

Contract, Month and Number of days in the month

They would have as many records as months that the contract is in effect.

Example:

Input file:

Contract, Start Date, End Date

120, 25/07/2015, 05/08/2015

132, 27/07/2105, 30/07/2015

154, 01/08/2015, 10/09/2015

178, 05/08/2015, 20/08/2015

And the expected result is:

Output file:

Contract, Month, Number of Days

120, 07, 07

120, 08, 05

132, 07, 04

154, 08, 31

154, 09, 10

178, 08, 16

Thanks in advance

Tags (2)
1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Re: Split an file

You can use a resident load with a WHILE clause - that will generate new rows as long as MonthStart() of the running month is less than the End Date.

Within the load, create your logical conditions using the IterNo() function, which gives you the number of each iteration. You can calculate the running Month and the number of days that are active in that month.

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

View solution in original post

2 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Split an file

You can use a resident load with a WHILE clause - that will generate new rows as long as MonthStart() of the running month is less than the End Date.

Within the load, create your logical conditions using the IterNo() function, which gives you the number of each iteration. You can calculate the running Month and the number of days that are active in that month.

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

View solution in original post

Highlighted
ecolomer
Honored Contributor II

Re: Split an file

This is the example:

P_Split.png