Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Loading data only for recent 3 years

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

i got all values ...even i got missing dates also..!! need to change date format in script..

i.png

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

iiii.png 

iii.png

thanks

venky

1 Solution

Accepted Solutions

Re: Loading data only for recent 3 years

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

13 Replies

Re: Loading data only for recent 3 years

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

Not applicable

Re: Loading data only for recent 3 years

Thanks bro..

how to change to time format?

Re: Loading data only for recent 3 years

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

Not applicable

Re: Loading data only for recent 3 years

thanks bro

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

Re: Loading data only for recent 3 years

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

Not applicable

Re: Loading data only for recent 3 years

where should i keep this syntax..?

Re: Loading data only for recent 3 years

‌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

Not applicable

Re: Loading data only for recent 3 years

i got it bro... thanks alot

Re: Loading data only for recent 3 years

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

Community Browser