topic Re: dates in between in QlikView

dates in between

alec1982 — Wed, 25 Nov 2020 16:16:04 GMT

have a table with the following month close dates

8/28/2017

9/21/2017

10/28/2017

need to add a new column with all dates in between so I can see all dates related to the month close dates as follow

8/28/2017 8/1/2017

8/28/2017 8/2/2017

8/28/2017 8/3/2017

...

8/28/2017 8/28/2017

9/21/2017 8/29/2017

9/21/2017 8/30/2017

9/21/2017 8/31/2017

9/21/2017 9/1/2017

...

9/21/2017 9/21/2017

and so on..

any quick solution you can share?

Re: dates in between

Anil_Babu_Samineni — Thu, 19 Oct 2017 03:10:33 GMT

Like this?

SET DateFormat='M/D/YYYY';

LOAD FieldName, MonthStart, Date(MonthStart + IterNo() - 1) as Date

While Date(MonthStart + IterNo() - 1) <= FieldName;

LOAD *, MonthStart(FieldName) as MonthStart Inline [

FieldName

8/28/2017

9/21/2017

10/28/2017

];

Re: dates in between

alec1982 — Thu, 19 Oct 2017 03:26:29 GMT

almost right but it is not. We dont need to use month start. the first date here on my sample happen to be a month start but if you look at the dates between august and September.. month start doesnt work. let me know if you know how to adjust the logic

Re: dates in between

Anil_Babu_Samineni — Thu, 19 Oct 2017 03:51:05 GMT

Not sure, I followed. Can you make output for one date to test

Re: dates in between

alec1982 — Thu, 19 Oct 2017 03:57:52 GMT

if you take this example. if you notice when the first column value is 9/21/2017, the second column shows next value (8/29/2017) which is next value after 8/28/2017 showing above 9/21/2017

8/28/2017 8/28/2017

9/21/2017 8/29/2017

9/21/2017 8/30/2017

9/21/2017 8/31/2017

9/21/2017 9/1/2017

...

9/21/2017 9/21/2017

Re: dates in between

Anil_Babu_Samineni — Thu, 19 Oct 2017 04:05:29 GMT

Still not sure, When i say i am not understand your output. You shouldn't copy paste from previous one. I would request you to share sample output in excel from given data..

Re: dates in between

alec1982 — Thu, 19 Oct 2017 04:10:25 GMT

the sample I provided is very clear.. sorry you cannot understand it and thank you or your input. will wait for others to take a look.

thanks again.

Re: dates in between

Anil_Babu_Samineni — Thu, 19 Oct 2017 04:15:13 GMT

Yes, We will wait for other reply. You are not provided answer for 10/28/2017

Are you looking for this? It captures from providing data as 8,9,10 months whole data..

LOAD FieldName, MonthStart, Date(MonthStart + IterNo() - 1) as Date

While Date(MonthStart + IterNo() - 1) <= MonthEnd(FieldName);

Re: dates in between

jonathandienst — Thu, 19 Oct 2017 04:55:12 GMT

How about interval matching the date range from previous month close(+1) to month close:

// Load the month close dates

MonthCloses:

LOAD Date(Date#(MonthClose, 'M/dd/yyyy')) as MonthClose

INLINE

[

MonthClose

8/28/2017

9/21/2017

10/28/2017

];

// Create a match table for the month ranges

Match:

LOAD Alt(Previous(MonthClose) + 1, 0) as FromDate,

MonthClose as ToDate

Resident MonthCloses;

Fact: // load your fact table with the date field....

LOAD

...

Date,

...

;

// Apply the month table to the fact

Left Join(Fact)

IntervalMatch(Date)

LOAD FromDate, ToDate as CloseDate

Resident Match;

// Cleanup

DROP Field FromDate;

DROP Tables MonthCloses, Match;