Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have to merge three dates into one date: order date, start date, and finish date and I have to merge all of these three dates into one date called the implementation date. I have been suggested master calender and canonical date. I am trying to use master calendar but it keeps running into an error.
Temp:
Load
min(orderdate) as minDate,
max(orderdate) as maxDate
Resident mytable;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
orderdateCalendar:
Load
TempDate AS orderdate,
week(TempDate) As orderdateWeek,
Year(TempDate) As orderdateYear,
Month(TempDate) As orderdateMonth,
Day(TempDate) As orderdateDay,
YeartoDate(TempDate)*-1 as orderdateCurYTDFlag,
YeartoDate(TempDate,-1)*-1 asorderdateLastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as orderdateRC12,
date(monthstart(TempDate), 'MMM-YYYY') as orderdateMonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as orderdateQuarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as orderdateWeekYear,
WeekDay(TempDate) as orderdateWeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
Thanks in advance,
seems to work for me:
d2:
LOAD Date(Today()-Ceil(Rand()*1000)) as linestartdate
AutoGenerate 100;
linestartdateCalendar:
Load linestartdate,
week(linestartdate) As linestartdateWeek,
Year(linestartdate) As linestartdateYear,
Month(linestartdate) As linestartdateMonth,
Day(linestartdate) As linestartdateDay,
YeartoDate(linestartdate)*-1 as linestartdateCurYTDFlag,
YeartoDate(linestartdate,-1)*-1 as linestartdateLastYTDFlag,
date(monthstart(linestartdate), 'MMM-YYYY') as linestartdateMonthYear,
Dual('Q'&Ceil(Month(linestartdate)/3),Ceil(Month(linestartdate)/3)) as linestartdateQuarter,
WeekName(linestartdate) as linestartdateWeekYear,
WeekDay(linestartdate) as linestartdateWeekDay;
LOAD Date(minDate + IterNo() - 1) as linestartdate
While minDate + IterNo() - 1 <= maxDate;
Load min(linestartdate) as minDate,
max(linestartdate) as maxDate
Resident d2;
please post an example where it doesn't work.
thanks
regards
Marco
What is the exact error you are getting?
Does your mytable table actually have an orderdate?
Is it spelled orderdate, OrderDate or Orderdate?
Is your mytable spelled mytable or myTable?
Did you floor() that date field?
date(orderdate) as orderdate
Yes mytable has an orderdate but it shows up as an integer so I have to convert it into a date. It is spelled orderdate and mytable. How would I use the floor () function for this scenario?
Also find attached my errors.
Thanks,
Based on the first error, your "mytable" table seems not to exist. Check if you are spelling that name 100% correct. Please note case sensitivity.
All other errors are the result of the first error.
A date is a number. No need to change the looks of it to a date (as that is that date() does; just formatting).
To floor that thing, type this:
floor(orderdate) as orderdate
But again, the problem is that your "mytable" does not exists, most probably due to it having a different name.
Also, change this line:
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
to this line:
AUTOGENERATE $(varMaxDate) - $(varMinDate) + 1;
in this case also use RecNo() instead of IterNo() in the rest of the TempCalendar Load.
regards
Marco
another version of a calendar script:
orderdateCalendar:
Load orderdate,
week(orderdate) As orderdateWeek,
Year(orderdate) As orderdateYear,
Month(orderdate) As orderdateMonth,
Day(orderdate) As orderdateDay,
YeartoDate(orderdate)*-1 as orderdateCurYTDFlag,
YeartoDate(orderdate,-1)*-1 asorderdateLastYTDFlag,
date(monthstart(orderdate), 'MMM-YYYY') as orderdateMonthYear,
Dual('Q'&Ceil(Month(orderdate)/3),Ceil(Month(orderdate)/3)) as orderdateQuarter,
WeekName(orderdate) as orderdateWeekYear,
WeekDay(orderdate) as orderdateWeekDay;
LOAD Date(minDate + IterNo() - 1) as orderdate
While minDate + IterNo() - 1 <= maxDate;
Load min(orderdate) as minDate,
max(orderdate) as maxDate
Resident mytable;
doesn't solve your issue with trying to load resident from table "mytable" that doesn't exist at this point of your script ...
hope this helps
regards
Marco
Hi Marco,
The below link doesn't work. This is for another date instead of the order date and it is in another table.
Field Name : linestartdate
Table Name: d2
linestartdateCalendar:
Load linestartdate,
week(linestartdate) As linestartdateWeek,
Year(linestartdate) As linestartdateYear,
Month(linestartdate) As linestartdateMonth,
Day(linestartdate) As linestartdateDay,
YeartoDate(linestartdate)*-1 as linestartdateCurYTDFlag,
YeartoDate(linestartdate,-1)*-1 as linestartdateLastYTDFlag,
date(monthstart(linestartdate), 'MMM-YYYY') as linestartdateMonthYear,
Dual('Q'&Ceil(Month(linestartdate)/3),Ceil(Month(linestartdate)/3)) as linestartdateQuarter,
WeekName(linestartdate) as linestartdateWeekYear,
WeekDay(linestartdate) as linestartdateWeekDay;
LOAD Date(minDate + IterNo() - 1) as linestartdate
While minDate + IterNo() - 1 <= maxDate;
Load min(linestartdate) as minDate,
max(linestartdate) as maxDate
Resident d2;
define "doesn't work"
seems to work for me:
d2:
LOAD Date(Today()-Ceil(Rand()*1000)) as linestartdate
AutoGenerate 100;
linestartdateCalendar:
Load linestartdate,
week(linestartdate) As linestartdateWeek,
Year(linestartdate) As linestartdateYear,
Month(linestartdate) As linestartdateMonth,
Day(linestartdate) As linestartdateDay,
YeartoDate(linestartdate)*-1 as linestartdateCurYTDFlag,
YeartoDate(linestartdate,-1)*-1 as linestartdateLastYTDFlag,
date(monthstart(linestartdate), 'MMM-YYYY') as linestartdateMonthYear,
Dual('Q'&Ceil(Month(linestartdate)/3),Ceil(Month(linestartdate)/3)) as linestartdateQuarter,
WeekName(linestartdate) as linestartdateWeekYear,
WeekDay(linestartdate) as linestartdateWeekDay;
LOAD Date(minDate + IterNo() - 1) as linestartdate
While minDate + IterNo() - 1 <= maxDate;
Load min(linestartdate) as minDate,
max(linestartdate) as maxDate
Resident d2;
please post an example where it doesn't work.
thanks
regards
Marco