Table not reading date dimension

[lostSquirrel112]

I have a data set with three different dates. I've created a master calendar to include all possible dates to be used as a table dimension. But when adding measures to the table the fields aren't respecting the date dimension and giving me the total count of the first measure for each row.

Sample Load Script:

dim_customer:

LOAD * INLINE [
Customer_ID,Signing_Date,Onboard_Date,Exit_Date
001,1/15/2018,2/23/2018,3/9/2019
002,3/9/2018,,
003,4/3/2018,4/11/2018,11/10/2018
004,6/12/2018,8/11/2018,12/15/2018
005,8/1/2018,10/3/2018,
006,3/3/2019,,
];

MinMaxDate:
Load Min([Signing_Date]) as MinDate, Max([Signing_Date]) as MaxDate resident [dim_customer];
Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;
Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate') ;
Drop Table MinMaxDate;

MasterCalendar:
Load Date,
Year(Date) as Year,
Month(Date) as Month,
Day(Date) as Day;

Load Date(recno()+$(vMinDate)) as Date Autogenerate vMaxDate - vMinDate;

Here is what I'm looking to accomplish:

But here's what is happening in Qlik Sense:

 

I know it's got to be something basic I must be missing but why aren't the measures respecting the dimension field?

[Vegar · Accepted solution]

Try to add a table like this to your data model. Then use CalendarType do distinguish which field you want to associate to the master calendar. 

For each _cal in 'Signing', 'Onboard', 'Exit'

CalendarLink:

LOAD Customer_ID, $(_cal)_Date as Date, '&(_cal)' as CalendarType

Resident dim_customer;

Next _cal

[Ksrinivasan]

hi,

in your script master calendar not associated with real data,

ksrinivasan

[lostSquirrel112]

Thank you so much! 

Adding this and changing the '&(_cal)' part to '$(_cal)' and then doing Count(calendarType) for my measures got this to work. Thanks again for taking the time to answer this for me!

[lostSquirrel112]

Thanks for the reply Ksrinivasan. Implementing Vegar's solution above linked the table like you're saying.

One followup to this, In the future, how can I manually link a table on different keys since these tables don't show up in data manager as they are added in the load script.

[Ksrinivasan]

hi,

you have to store load script result in CSV, then verify your expected result would be present,

ksrinivasan,