Master Calendar for date_range field

[Naresh1]

Hi all,

 I want to create master calendar for Date_Ranges field, in that field having values like this '1-12-2019 to 12-12-2019',

'13-12-2019 to 26-12-2019',

how can i do

i am trying to do this below script, after re loding it shows error

Min_Max:

Load

Min(Date_Ranges) as MinDate,

Max(Date_Ranges) as MaxDate

Resident cost;

Let vMinDate =Num(Peek('MinDate',0,'Min_Max'));

Let vMaxDate = Num(Peek('MaxDate',0,'Min_Max'));

drop table Min_Max;
DateRanges:
Load
$(vMinDate)+IterNo()-1 as DateNum,
Date($(vMinDate)+IterNo()-1) as TempDate
AutoGenerate 1 while $(vMinDate)+IterNo()-1 <= $(vMaxDate);

Master_Calendar:

Load
TempDate as Date_Ranges,

MonthName(TempDate) as MonthName,

QuarterName(TempDate) as QuarterName,

YearName(TempDate) as YearName,

'Q'&Ceil(Month(TempDate)/3) as Quarter,

Year(TempDate) as Year,

Month(TempDate) as Month,

WeekDay(TempDate) as WeekDay

Resident DateRanges Order by TempDate asc;

drop table DateRanges;

regards,

[jonathandienst]

>>i am trying to do this below script, after re loding it shows error

It would help if you could describe the error a little, so that people don't need to do detective work to understand the problem...

[Naresh1]

Here is the error what i got after reloding the data

[jonathandienst]

The dates are being read as strings. You need to help QV convert them into dates:

Load
    Min(Date#(Date_Ranges, 'dd-MM-yyyy')) as MinDate,
    Max(Date#(Date_Ranges, 'dd-MM-yyyy')) as MaxDate
Resident cost;

This assumes the dates are strings in the format dd-MM-yyyy.  If that does not work, I suggest you extract a sample of the Date_Ranges field  to investigate further.

[Naresh1]

Hi,

My manager just now told me , Split these values then create master calendar for from_date field

I have Date Field in that field having values like '1.10.2019 to 12.10.2019'

                                                                                                 ' 13.10.2019 to 26.10.2019'

i want to split this values into two fields,

                                                                                 From_Date field:                                 To_Date:

                                                                                     '1.10.2019'                                         '12.10.2019'

                                                                                     ' 13.10.2019'                                    ' 26.10.2019'

How can i do?