Split an file

[ecolomer]

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

[Oleg_Troyansky · Accepted solution]

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

[ecolomer]

This is the example: