Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
gorterzelf7
Partner - Creator
Partner - Creator

Create startdate and enddate from samefield

 

Hello all,

I have a table where the startdate and the enddate is the same field. 

In fact i wanna join this to another table where every date is filled. 

So I would like to see all the date between startdate and enddate.  

I have pasted an example. 

Article StartDate EndDate Value

1 02-01-2017 02-01-2017 9
1 24-07-2017 24-07-2017 10
1 01-01-2018 01-01-2018 11
1 03-12-2018 03-12-2018 12
1 25-02-2019 25-02-2019 9 
1 03-06-2019 03-06-2019 10
1 09-09-2019 09-09-2019 20
1 02-12-2019 02-12-2019 5

I got this, but don't know how to fix it to see all the dates in between.

DateOrderLink:
LOAD 
Article,
Date(StartDate + IterNo() - 1) as Date
RESIDENT Test
While Date(StartDate + IterNo() - 1) <= EndDate ;

 

I hope somebody is able to help me.

Labels (2)
4 Replies
Anil_Babu_Samineni

My Suggestion is like this?

Table:

Load StartDate as Date From T1;

Join

Load EndDate as Date From T1;

Calendar:

Load Date;

Load MinDate + IterNo() - 1 as Date while MinDate + IterNo() - 1 <= Num(MaxDate);

MinMax:

Load Min(Date) as MinDate, Max(Date) as MaxDate Resident Table;

 

Temp:

Load Distinct Date as StartDate

Resident Calendar;

Join

Load Distinct Date as TpDate

Resident Calendar;

 

Date_Link:

Load Distinct StartDate as Date

Resident Temp;

Inner Join IntervalMatch(Date)

Load *

Resident Temp

Where StartDate <= ToDate ;

Drop Table Temp;

 

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
gorterzelf7
Partner - Creator
Partner - Creator
Author

Thank you so much for your answer @Anil_Babu_Samineni 🙂 Did you try to run this? When i run this it says Calender not exists.

Could you please be so kind to take another look. It is really amazing how fast you came up with this:-)

 

gorterzelf7
Partner - Creator
Partner - Creator
Author

Or maybe somebody else can help me with the script?Smiley LOL

gorterzelf7
Partner - Creator
Partner - Creator
Author

@Anil_Babu_Samineni could you please take a look i think we are pretty close:-)