topic Re: date between another date range in App Development

date between another date range

rohit_bibe07 — Thu, 22 Sep 2022 04:44:43 GMT

col1_date in between [col2_date <=15 and col2_date >=1]

I want col1_date between col2_date <=15 and col2_date >=1 this range please provide some logic to build

Regards,

Rohit

Re: date between another date range

Dolphin — Thu, 22 Sep 2022 07:24:14 GMT

Hi Rohit,

can you clarify your requirements a little bit more?

You have two date columns. Is [col2_date <= 15 and col2_date >= 1] the name of the second or do you want it to be in that range?

And what do you mean by col1_date should be in between that field? Do you want all dates in between that range or do you want to check if col1_date is in between the range.

Let's assume you have a column with a date range and want to check if col1_date is in between that range:

If you don't have the from and to dates you can calculate them like this (with the range looking like '2022/01/01-2022/01/15'):

Date#(SubField(col2_date, '-', 1), 'YYYY/MM/DD') AS FromDate, Date#(SubField(col2_date, '-', 2), 'YYYY/MM/DD') AS ToDate,

Then you can just do a simple If Condition:

If(col1_date >= FromDate and col1_date <= ToDate, 1, 0) AS isInBetween

If you want to get all dates between the range you could use a mastercalendar and intervalmatch:

AllDates: Load DateKey; //You can calculate all time Fields with this date Load Date(MinDate + IterNo() - 1) AS DateKey While (MinDate + IterNo() -1) <= MaxDate; Load Min(FromDate) as MinDate, Max(ToDate) as MaxDate Resident Source; Left Join(Source) //Based on FromDate and ToDate DatesBetween: IntervalMatch(DateKey) Load Distinct FromDate, ToDate Resident Source;

Kind Regards,

Johannes

Re: date between another date range

rohit_bibe07 — Thu, 22 Sep 2022 08:28:52 GMT

Hi,

Johannes thanks for your answer, my requirement is that i want date range from [col2_date<=15 and col2>=1], col2_date is date column like 'DD-MM-YYYY' and another condition is that i want to filter date of col1_date with respect to above condition means if user select date from col2_date then col1_date will be filtered. so the overall condition is that "col1_date inbetween [col2_date<=15 and col2>=1] in this range.

Regards,

Rohit

Re: date between another date range

Dolphin — Thu, 22 Sep 2022 10:19:10 GMT

What do you mean with col2_date<=15? Do you want to check if the date is >= 15 (Date(15) would return 1900/01/14 as a date)? Or do you want the Date of 15 day ago compared to col2_date?

If you have col2_date and want to create a range with it you could do something like this:

Load col1_date, col2_date, Date(col2_date -15) & AS FromDate, Date(col2_date +1) AS ToDate Date(col2_date -15) & Date(col2_date +1) as [col2_date<=15 and col2_date>=1] From Source

With these new Dates (FromDate & ToDate) you can again just check if col1_date is in between by creating the If condition in the previous answer.

Re: date between another date range

Dolphin — Fri, 23 Sep 2022 07:09:47 GMT

Hi again,

I thought of a use case you might mean. Pls Check the script. Is this the right Direction?

Basically you can select Date2 as ReferenceDate and get all Date1s that are in between the ReferenceDates Range (and the same Dim!):

Source: Load * Inline [ Dim, Date1, Date2 A, 2022/01/15, 2022/01/09 A, 2022/01/27, 2022/02/09 A, 2022/02/02, 2022/02/10 B, 2022/03/09, 2022/07/12 B, 2022/07/09, 2022/07/12 C, 2022/03/04, 2022/03/03 ]; NoConcatenate Fact: Load Dim, Date#(Date1, 'YYYY/MM/DD') AS Date1, Date#(Date2, 'YYYY/MM/DD') AS Date2 Resident Source; Drop Table Source; Time_Temp: Load Dim, Date2 AS ReferenceDate, Date(Date2 - 15, 'YYYY/MM/DD') AS DateFrom, Date(Date2 + 1, 'YYYY/MM/DD') AS DateTo Resident Fact; Left Join(Time_Temp) IntervalMatch(Date1, Dim) Load DateFrom, DateTo, Dim Resident Time_Temp; Time: Load Date1, ReferenceDate, DateFrom, DateTo, Dim as ReferenceDim Resident Time_Temp; Drop Table Time_Temp;

Kind Regards,

Johannes