topic Re: Date generation in App Development

Date generation

Thu, 14 Apr 2016 09:12:09 GMT

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

Re: Date generation

sunny_talwar — Thu, 14 Apr 2016 10:52:48 GMT

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

];

Re: Date generation

Thu, 14 Apr 2016 12:38:24 GMT

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

Re: Date generation

sunny_talwar — Thu, 14 Apr 2016 12:50:36 GMT

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);

Re: Date generation

Thu, 14 Apr 2016 13:04:20 GMT

Works just fine. Thanks a lot.

Mathieu

Re: Date generation

oknotsen — Thu, 14 Apr 2016 13:11:11 GMT

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 .