Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
sorry i'm still a newbie..
I'm trying to link Quarter and month together. The quarter is from a different table file (I had to create an extra excel sheet to link month and quarter because a customer counts quarter differently, (see below), but that shoudn't be a problem since the table file also has month names and thus links the two tables together.
Feb till Apr - Q1
May till Jul - Q2
Aug till Okt - Q3
Nov till Jan - Q4
Script:
MasterFile:
LOAD
Date(Date) as Date,
Year(Date)as Year,
Month(Date) as Month,
FROM
a.xls
Calender:
LOAD
Month(Month) as Month,
Quarter
FROM
x.xls
When I look at my script, it seems correct. But there still no link. If I click on aug I should see Q3. but that doesn't work.
What am I doing wrong?
Cheers!
Hi,Try to load full calendar like this
[Calendar]:
LOAD[DateField],
DAY([DateField]) AS [Day],
WEEKDAY([DateField]) AS [WeekDay],
WEEKDAY([DateField]) &' - '& DAY([DateField]) AS [Day-WeekDay],
WEEK([DateField]) AS [Week],
'W' & WEEK([DateField]) &' - '& YEAR([DateField]) AS [Week-Year],
NUM(MONTH([DateField])) AS [MonthNum],
MONTH([DateField]) AS [Month],
MONTH([DateField]) &' - '& YEAR([DateField]) AS [Month-Year],
'Q' & CEIL(MONTH([DateField])/3) AS [Quarter],
'Q' & CEIL(MONTH([DateField])/3) &' - '& YEAR([DateField]) AS [Quarter-Year],
YEAR(DATE([DateField])) AS [Year]FROM tblName;
Let me know if this one help you.
Regards,
Sokkorn
Hi
Have you checked that the two load statements are returning equivalent data. There are serveral reasons why they may not be, including:
Be careful of using Month to link these tables - this will link January 2011 and January 2010 to the same quarter. The common practice is to link on Date and create a master calendar along the lines of the previous post.
Hope this helps
Jonathan
Hi Sokkorn
Thanks for your help!! And sorry for the late response. Loading a full calendar works. However, I still need to find a way to group aug, sept, okt as a third quarter. Normally the third quarter should be juli, aug and sept. Unfortunately the customers wants it differently, that’s why I need to find a solution.
Thanks in advance!!!
isam
Hi Jonathan
Thanks for your help!! You are right!!! I shouldn’t be linking month with month. Thanks for the Eye-Opener!!
I think the reason why there was no link is because of the different date format.
Thanks!
iSam