Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
This is the example: