Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I have the foloowing table (simplified of course):
Code | FROM_date | TO_date | Manager |
---|---|---|---|
100 | 01 - 01 - 2016 | 03 - 01 - 2016 | Hank |
101 | 01 - 01 - 2016 | 05 - 01 - 2016 | Edward |
Now what i want to make is the following table
DAY-CODE-KEY | Manager |
---|---|
01-01-2016-100 | Hank |
02-01-2016-100 | Hank |
03-01-2016-100 | Hank |
01-01-2016-101 | Edward |
02-01-2016-101 | Edward |
03-01-2016-101 | Edward |
04-01-2016-101 | Edward |
05-01-2016-101 | Edward |
So basically i want to load each row in the top-table and then use a FOR-loop to create rows in a new table.
I was thinking to loop through the lines, then set two vars with peek (from and to), and then loop from 'from' to 'to' and create the lines in table 2.
Am I thinking in the wrong direction? Cause I can't seem to nest two for statements....
Kind regards,
Herb
Another options:
Intervals:
LOAD Date,
Code,
Date & '-' & Code as [DAY-CODE-KEY],
Manager;
LOAD Date(FROM_Date + IterNo() - 1) as Date,
Code,
Manager
While FROM_Date + IterNo() - 1 <= TO_date;
Load * Inline [
FROM_Date, TO_date, Code, Manager
01-01-2016, 03-01-2016, 100, Hank
01-01-2016, 05-01-2016, 101, Edward
];
Herb,
I think you can use the intervalmatch function. Take a look at this script and see if it can help you along the way.
best regards,
Helena
Another options:
Intervals:
LOAD Date,
Code,
Date & '-' & Code as [DAY-CODE-KEY],
Manager;
LOAD Date(FROM_Date + IterNo() - 1) as Date,
Code,
Manager
While FROM_Date + IterNo() - 1 <= TO_date;
Load * Inline [
FROM_Date, TO_date, Code, Manager
01-01-2016, 03-01-2016, 100, Hank
01-01-2016, 05-01-2016, 101, Edward
];
Thanks, this time I chose the iterative solution provided by Sunny.
Thanks for the example of proper use of an interval function.
Thanks, works exactly as needed.