Selecting dates

[MK9885]

Hello,

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.

1. ActionDate

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.

stalwar1‌swuehl‌

It would be great if anyone could help with this, Thanks.

[robert_mika]

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?

[shraddha_g]

instead of auto generating dates in master calendar you can take dates from fact table.

For Ex.

Fact_table:

Load Field1,

         Field 2,

          Field 3,

           ActionDate as CalendarDate

From ..../fact.qvd;

Master_Calendar:

Load Distinct ActionDate as CalendarDate,

          Year(ActionDate) as Year,

          Month(ActionDate) as Month

Resident Fact_table;

This way it will be linked with your fact table.

I hope this is helpful.

[lakshmikandh]

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.

For Ex.

Fact_table:

Load Field1,

         Field 2,

          Field 3,

           ActionDate ,

           InitDate,

          etc

From ..../fact.qvd;

Action_Calendar:

Load Distinct ActionDate ,

          Year(ActionDate) as YAction_Year,

          Month(ActionDate) as Action_Month

Action_Calendar:

Load Distinct InitDate ,

          Year(InitDate ) as Init_Year,

          Month(InitDate ) as Init_Month

Hope this helps

Thanks,

Lakshmikandh

[MK9885]

I tried this and not working. As we changing the name ActionDate to CalenderDate in fact, it is not reading it in Master_Calendar Table you said.

[MK9885]

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.

[#7]

How about Henric Cronströms canonical date approach?

Canonical Date

[lakshmikandh]

Yes, you @arvind654can try this Canonical Date

[MK9885]

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.

[MK9885]

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.