Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
>>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...
Here is the error what i got after reloding the data
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.
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?