Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
Here is my problem, I have 3 columns, 1 ID, 1 Start date and 1 End date from an Excel spreadsheet. What I would like to is create a table in the script with 2 columns: the ID duplicated and the detail of each day (example hereafter).
Operation | Start | End |
A | 01/03/2016 | 04/03/2016 |
B | 08/09/2017 | 12/09/2017 |
==>
Operation | Date | |
A | 01/03/2016 | |
A | 02/03/2016 | |
A | 03/03/2016 | |
A | 04/03/2016 | |
B | 08/09/2017 | |
B | 09/09/2017 | |
B | 10/09/2017 | |
B | 11/09/2017 | |
B | 12/09/2017 |
This is my first post, so please let me know if I did something wrong.
Thanks in advance
Mathieu
Inline was just an example to show how this can be done. Try this:
[shopfloor]:
LOAD Operation,
Date(Start + IterNo() - 1) as Date
While Start + IterNo() - 1 <= End;
LOAD
[Operation],
[Start],
[End]
FROM [lib://Desktop/Myexcelfile.xlsx]
(ooxml, embedded labels, table is sheet1);
Try this:
Table:
LOAD Operation,
Date(Start + IterNo() - 1) as Date
While Start + IterNo() - 1 <= End;
LOAD * Inline [
Operation, Start, End
A, 01/03/2016, 04/03/2016
B, 08/09/2017, 12/09/2017
];
Dear Sunny T
Thanks for your quick answer. Unfortunately, I am not able to apply it because the data I want to load come from an excel file and I need to calculate the dates directly from the loaded data. I should have been more precise in the statement of my issue, apologies. Hereafter is the code I use
[shopfloor]:
LOAD
[Operation],
[Start],
[End],
//Date("Start" + IterNo() - 1) as "Newdate"
//While "Start" + IterNo() - 1 <= "End"
FROM [lib://Desktop/Myexcelfile.xlsx]
(ooxml, embedded labels, table is sheet1);
Thanks for your help
Inline was just an example to show how this can be done. Try this:
[shopfloor]:
LOAD Operation,
Date(Start + IterNo() - 1) as Date
While Start + IterNo() - 1 <= End;
LOAD
[Operation],
[Start],
[End]
FROM [lib://Desktop/Myexcelfile.xlsx]
(ooxml, embedded labels, table is sheet1);
Works just fine. Thanks a lot.
Mathieu
If your question is now answered, please flag the Correct Answer.
If not, please let us know what part of this topic you still need help with .