topic Re: Generate missing dates in App Development

Generate missing dates

agni_gold — Thu, 12 Jul 2018 14:53:24 GMT

Hi Friends,

I have a data like below ,

Name	Date
A	13-Apr-17
B	13-Jul-17
C	13-Oct-17
D	13-Oct-18

Now i want to generate dates between the name values, like A should be repeated till June month and from July B will repeat till Sep and from Oct 2017 to Sep 2018 C should repeat.

Can you please tell how can i generate this in script.

Re: Generate missing dates

m_woolf — Thu, 12 Jul 2018 15:08:38 GMT

This is specific to QlikView but may also work in Sense

Generating Missing Data In QlikView

Re: Generate missing dates

agni_gold — Thu, 12 Jul 2018 15:19:57 GMT

Thanks, But i have already tried this, it generates the same dates from each of the value, but in my requirement, there is conditional dates generation.

Re: Generate missing dates

agni_gold — Thu, 12 Jul 2018 16:05:47 GMT

hic‌ can you please comment on my question please ?

Re: Generate missing dates

hic — Fri, 13 Jul 2018 07:56:29 GMT

It is unclear to me if you want the periods to start and end at month starts, or at the dates listed in your table. But if I assume that you indeed want to use the dates listed, the following script will do the job:

// ===========================
SourceData:
Load Name,
Date(Date#(Date,'DD-MMM-YY')) as DateInSourceData
Inline
[Name,Date
A,13-Apr-17
B,13-Jul-17
C,13-Oct-17
D,13-Oct-18];

tmpData:
Load
DateInSourceData as BeginDate,
Date(Floor(Peek(BeginDate)-0.0001)) as EndDate,
Name
Resident SourceData
Order By DateInSourceData Desc;

Dates:
Load
Date(BeginDate + IterNo() - 1) as Date,
*
Resident tmpData
While BeginDate + IterNo() - 1 <= EndDate;

Drop Table tmpData;

// ===========================

But if you instead want to use the month starts, you just change

DateInSourceData as BeginDate,

MonthStart(DateInSourceData) as BeginDate,

HIC