Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hello friends, i got a issue while loading data from master calendar..
i need to load Date recent 3 three years..with filling missing dates..
so far i got this result...pls check...
i got all values ...even i got missing dates also..!! need to change date format in script..
2) When i load that only for recent 3 years...iam missing dates..please someone help me to filling values and setting variable for year for recent year...
thanks
venky
From what I see, you are restricting only you master calendar to be include data from 2009 and forward, but the fact table may still have those have that date. You can restrict your fact table by using a Right Keep. Try this:
MasterCalendar:
Right Keep (FactTable) //Assuming that the fact table is loaded before Master Calendar
LOAD OrderDate,
Week,
Year,
Month,
MonthNo,
DayofYear,
MonthYear,
YearMonth,
Day,
Quarter,
YearWeek,
WeekDay,
WeekDayNo,
WeekInMonth,
CurYTDFlag,
LastYTDFlag,
CurMTDFlag,
LastMTDFlag
FROM
Where Year >= 2009;
See if this helps
Best,
Sunny
From what I see, you are restricting only you master calendar to be include data from 2009 and forward, but the fact table may still have those have that date. You can restrict your fact table by using a Right Keep. Try this:
MasterCalendar:
Right Keep (FactTable) //Assuming that the fact table is loaded before Master Calendar
LOAD OrderDate,
Week,
Year,
Month,
MonthNo,
DayofYear,
MonthYear,
YearMonth,
Day,
Quarter,
YearWeek,
WeekDay,
WeekDayNo,
WeekInMonth,
CurYTDFlag,
LastYTDFlag,
CurMTDFlag,
LastMTDFlag
FROM
Where Year >= 2009;
See if this helps
Best,
Sunny
Thanks bro..
how to change to time format?
You want to change it from MM/DD/YYYY hh:mm:ss TT to MM/DD/YYYY????
Try this:
Date(Floor(Date#(YourDate, 'MM/DD/YYYY hh:mm:ss TT')), 'MM/DD/YYYY') as YourDate
thanks bro
how to allocate variable to Max(Year)...and call this variable in where clause?
Try this:
Temp:
LOAD Max(Year) as maxYear
Resident YourMasterCalendarTableName;
LET vMaxYear = Num(Peek('maxYear', 0, 'Temp'));
DROP Table Temp;
Now you can use $(vMaxYear) in the Where clause.
HTH
Best,
Sunny
where should i keep this syntax..?
it needs to be after the table from which you need the max year information from and before the table where you would be restricting the date. I would need to see your complete script to give you a better suggestion.
Best,
Sunny
i got it bro... thanks alot
No problem bud ... I am glad I was able to help.