Storing Date or month in a variable

[CRESA Dept Expenses Temp]:
LOAD Country, 
 Category, 
 MonthName, 
 Dept_Expenses
FROM
[..\QVD\CRESA_Dept_Expenses.qvd]
(qvd);

[CRESA Dept Expenses]:
load *,ApplyMap('MappingLoad', Country, null())&[Month Name] as Key;//,year([Month Name]) as Year,month([Month Name]) as Month;
Load 
Country,
//If(Len(Country)='', Peek(Country,-1),Country) as Country,
//If(Len(Dept_Expenses)=0, Peek(Dept_Expenses,-1),Dept_Expenses) as [Dept Expenses],
Dept_Expenses as [Dept Expenses],
Category as [Dept Category],
//MonthName,
//Dept_Expenses as [Dept Expenses],
MonthName(left(MonthName,5)) as [Month Name],
if(len(MonthName)=5,'Actual',
if(len(MonthName)=6 and right(MonthName,1)=1,'Budget',
if(len(MonthName)=6 and right(MonthName,1)=2,'Forecast'
))) as [Dept Flag]
Resident [CRESA Dept Expenses Temp];

drop table [CRESA Dept Expenses Temp];
//EXIT Script;

HeadCount:
Load [Dept Flag],date(Date#([Month Name],'MMM YYYY'),'MMM-YY') as MonthName,[Dept Expenses]
Resident [CRESA Dept Expenses]
where len(trim([Dept Expenses]))>0 and [Dept Flag]='Actual'
order by [Month Name] desc;
LET vHeadCount=peek('MonthName',0,'HeadCount');

drop table HeadCount;

CRESADeptExpenses:

Load [Dept Flag],date(Date#([Month Name],'MMM YYYY'),'DD-MMM-YY') as MonthName,[Dept Expenses]//,max(date(Date#([Month Name],'MMM YYYY'),'DD-MMM-YY')) as MaxDate
Resident [CRESA Dept Expenses]
where len(trim([Dept Expenses]))>0 and [Dept Flag]='Actual'
//Group by [Dept Flag],[Month Name]
order by [Month Name] desc;
LET vCRESADeptExpenses=peek('MonthName',0,'CRESADeptExpenses');
LET vMaxDateDEPT=peek('MonthName',0,'CRESADeptExpenses');
drop table CRESADeptExpenses;
//EXIT Script;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.