I've created a Master calendar, find the attachment.
I want to map this Master calendar to my fact table with a key and no fields match with my fact table.
I tried to create DateID in my fact but the number of rows from Master doesn't match Fact.
I also tried using Trim(DateID) as Date_ID to only pick matching dates from Master but that didn't work too.
I want to pick a date from calendar in UI.
EX: If a user selects 08/21/2016 to 08/25/2016 it should only show the data for these 4 days on bar chart. Instead it is showing all dates.
Is there any way to pick dates or range dates from Master and also map it to fact?
In Fact I've 4 fields of date.
2. Initiation Date
3. Start Date
4. End Date
Also I'd like to join Start Date and End Date into one field. As in join. I used STARTDATE&'-'&ENDDATE as Date1, but the end result is those dates are everlaying upon each other but not joining.
Note: I'm working on Qlik Sense. and I'm using an extension object for Calender where we add a date dimension and it will give us calendar.
It would be great if anyone could help with this, Thanks.
The numbers of rows will not match as you probably may have more rows in your fact table that in calendar.
What is your linking field?
What format does it have?
Could you post your data/application?
instead of auto generating dates in master calendar you can take dates from fact table.
ActionDate as CalendarDate
Load Distinct ActionDate as CalendarDate,
Year(ActionDate) as Year,
Month(ActionDate) as Month
This way it will be linked with your fact table.
I hope this is helpful.
Yes, as mentioned by shraddha.g ,you have to keep the column name same by using alias then Qliksense will automatically sync the dates between tables. But as mentioned in your post you have 4 columns in that case you have to look for other alternatives as sync will happen for one to one.
May be load calendar 4 times and each time you sync the date field for one of your four columns.
Load Distinct ActionDate ,
Year(ActionDate) as YAction_Year,
Month(ActionDate) as Action_Month
Load Distinct InitDate ,
Year(InitDate ) as Init_Year,
Month(InitDate ) as Init_Month
Hope this helps
This created new Tables but not working either.
The year and month fields in this are having null values.It created fields and values which I already have in Fact Table.
There are less rows in Fact and more in Master
There is no key between fact or master.
My date format is YYYY-MM-DD in Master and in Fact it is DD-MMM-YY
I've already attached my Master script in my original post on top. For Fact data, sorry I can't.
I have one fact and it has 4 date fields
I just want to pick dates in UI.
EX: 08-21-2016 to 08-30-2016
The link which you gave has 4 different tables and quite confusing for me as I'm new to sense.