Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
alec1982
Specialist II
Specialist II

dates in between

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?

8 Replies
Anil_Babu_Samineni

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

];

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
alec1982
Specialist II
Specialist II
Author

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

Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
alec1982
Specialist II
Specialist II
Author

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

Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
alec1982
Specialist II
Specialist II
Author

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.

Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
jonathandienst
Partner - Champion III
Partner - Champion III

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;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein