Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
lostSquirrel112
Contributor II
Contributor II

Table not reading date dimension

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:

Screen Shot 2021-02-11 at 11.05.15 AM.png

 

But here's what is happening in Qlik Sense:

Screen Shot 2021-02-11 at 11.17.55 AM.png

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

 

1 Solution

Accepted Solutions
Vegar
Partner
Partner

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

5 Replies
Vegar
Partner
Partner

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

Ksrinivasan
Specialist
Specialist

hi,

in your script master calendar not associated with real data,

ksrinivasan

 

lostSquirrel112
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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

hi,

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

ksrinivasan,