Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

Date generation

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

 

OperationStartEnd
A01/03/201604/03/2016
B08/09/201712/09/2017

==>

 

OperationDate
A01/03/2016
A02/03/2016
A03/03/2016
A04/03/2016
B08/09/2017
B

09/09/2017

B10/09/2017
B11/09/2017
B12/09/2017

This is my first post, so please let me know if I did something wrong.

Thanks in advance

Mathieu

1 Solution

Accepted Solutions
MVP
MVP

Re: Date generation

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

5 Replies
MVP
MVP

Re: Date generation

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

];

Not applicable

Re: Date generation

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

MVP
MVP

Re: Date generation

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

Not applicable

Re: Date generation

Works just fine. Thanks a lot.

Mathieu

oknotsen
Honored Contributor III

Re: Date generation

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 .

May you live in interesting times!