Loading data only for recent 3 years

[#1]

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

[sunny_talwar · Accepted solution]

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

(qvd)

Where Year >= 2009;

See if this helps

Best,

Sunny

[#3]

Thanks bro..

how to change to time format?

[sunny_talwar]

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

[#5]

thanks bro

how to allocate variable to Max(Year)...and call this variable in where clause?

[sunny_talwar]

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

[#7]

where should i keep this syntax..?

[sunny_talwar]

‌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

[#9]

i got it bro... thanks alot

[sunny_talwar]

‌No problem bud   ... I am glad I was able to help.