topic Re: Master Calendar for date_range field in App Development

Master Calendar for date_range field

Naresh1 — Thu, 06 Feb 2020 07:09:06 GMT

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,

Re: Master Calendar for date_range field

jonathandienst — Thu, 06 Feb 2020 07:34:53 GMT

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

Re: Master Calendar for date_range field

Naresh1 — Thu, 06 Feb 2020 07:58:12 GMT

Here is the error what i got after reloding the data

Re: Master Calendar for date_range field

jonathandienst — Thu, 06 Feb 2020 10:58:42 GMT

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.

Re: Master Calendar for date_range field

Naresh1 — Thu, 06 Feb 2020 11:09:22 GMT

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?