Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Using the Master Calendar to load the date field in the form of Day, Week, Year, etc shows a dash in some records while if I create an additional dimension for that record using the Day(), Week(), or Year(), then it works. Is there something wrong in my script?
LOAD
"OPEN_TIME" as PROpenTime,
Date( Floor( "OPEN_TIME" ) ) as PROpenDate;
SQL SELECT * FROM databasename;
// The Master Calendar
Load
PROpenDate,
week( PROpenDate ) As PROpenDateWeek,
Year( PROpenDate ) As PROpenDateYear,
Month( PROpenDate ) As PROpenDateMonth,
Day( PROpenDate ) As PROpenDateDay,
YeartoDate( PROpenDate )* -1 as PROpenDateCurYTDFlag,
YeartoDate( PROpenDate , -1) * -1 as PROpenDateLastYTDFlag,
date( monthstart( PROpenDate ), 'MMM-YYYY' ) as PROpenDateMonthYear,
ApplyMap( 'QuartersMap' , month( PROpenDate ), Null() ) as PROpenDateQuarter,
Week( weekstart( PROpenDate ) ) & '-' & WeekYear( PROpenDate ) as PROpenDateWeekYear,
WeekDay( PROpenDate ) as PROpenDateWeekDay
Resident themaintable;
Here is a sample of the outcome:
You will notice that the second record is working properly while the first one is not.
Thank you for your response in advance.
Hello again,
Here is a trimmed version of my application.
Thank you in advance,
Ibrahim
Hi there was a mistake in my script. '*' keyword was missing in calendar preceding load, due to which calendar and problem table was not linked on Date. Try below it should work
PrbCalendar:
LOAD *,
week( PROpenDate ) As PROpenDateWeek,
Year( PROpenDate ) As PROpenDateYear,
Month( PROpenDate ) As PROpenDateMonth,
Day( PROpenDate ) As PROpenDateDay,
YeartoDate( PROpenDate )* -1 as PROpenDateCurYTDFlag,
YeartoDate( PROpenDate , -1) * -1 as PROpenDateLastYTDFlag,
date( monthstart( PROpenDate ), 'MMM-YYYY' ) as PROpenDateMonthYear,
ApplyMap( 'QuartersMap' , month( PROpenDate ), Null() ) as PROpenDateQuarter,
Week( weekstart( PROpenDate ) ) & '-' & WeekYear( PROpenDate ) as PROpenDateWeekYear,
WeekDay( PROpenDate ) as PROpenDateWeekDay;
LOAD date($(vPROpenMinDate)+iterno()-1) as PROpenDate
autogenerate 1
While $(vPROpenMinDate)+iterno()-1 <= $(vPROpenMaxDate);
This is the facts table. Is this normal?
The count of ID is 3 which is correct but one about the remaining NULL rows?
Hi again.. I also would like to clarify that the following expression is not counting the number correctly:
=Count( { $<PROpenDateMonthYear={"$(=Max( PROpenDateMonthYear ))"}> } ID )
It is showing 7 not 3.
In your calendar you are generating all the dates between min and max date. So any date which is not available in your Fact will be shown as NULL which is normal.
what are the values in PROpenDateMonthYear?
try this
=Count( { $<PROpenDateMonthYear={"$(=date(Max( PROpenDateMonthYear ),'MMM-YYYY'))"}> } ID )
It's returning 0. I have also tried this and it's still giving me 7:
=Count( { $<PROpenDateMonthYear={'Mar-2017'}> } ID )
Can you post your application?