Canonical Dates for 1 table

[#1]

Is it possible to create canonical date when all your information is located in 1 table?

I'm trying to create Line charts with 6 different values. I have created Master Calendar which is not so helpful.

Please suggest, if possible to create a canonical date when all your information is located in 1 table.

Thanks in Advance

-Dushyant

[swuehl]

I hope I understand your request correctly, but I would say, sure, creating a canonical date based on dates located in one table should be easy.

If you compare to

Canonical Date

Henric is basically chosing one table with fine enough granularity and brings all dates into that table.

If you need more help, then please post at least the structure of your table (i,e the fields), maybe also some sample data.

[#3]

Can you please look at the below script where [D_CREATE_DATE] & [D_EST_RECV_DATE] is coming from the same table.

Is this the right approach??

invitation_id2Date:

     Mapping Load [I_MASTER_ID],[D_CREATE_DATE] Resident MEDDS;

invitation_id2date_accessed:

     Mapping Load [I_MASTER_ID],[D_EST_RECV_DATE] Resident MEDDS;

DateBridge:

     Load [I_MASTER_ID], Applymap('invitation_id2Date',[I_MASTER_ID],Null()) as CanonicalDate, 'CreateTest' as DateType

          Resident MEDDS;      

     Load [I_MASTER_ID], Applymap('invitation_id2date_accessed',[I_MASTER_ID],Null()) as CanonicalDate, 'EstTest' as DateType

          Resident MEDDS ;  

[#4]

My table structure looks something like this, there are more columns, but I'm only interested in below columns.

I want create line chart using canonical date, I have been unsuccessful so far.

I_MASTER_ID	D_CREATE_DATE	D_EST_RECV_DATE	D_EST_SUB_DATE
111450	1/15/2014	1/30/2014	2/14/2014
111325	1/30/2014	2/14/2014	3/1/2014
111320	5/5/2014	5/20/2014	6/4/2014
111330	3/5/2016	3/20/2016	4/4/2016
111340	3/10/2017	3/25/2017	4/9/2017
111350	1/15/2014	1/30/2014	2/14/2014
111360	1/30/2014	2/14/2014	3/1/2014
111370	5/5/2014	5/20/2014	6/4/2014

Please suggest some ideas.

Thanks

[swuehl · Accepted solution]

Yes, but I think you don't need the ApplyMap() here, since all information is already in the table MEDDS. Just do

DateBridge:

LOAD [I_MASTER_ID], [D_CREATE_DATE] as CanonicalDate, 'CreateTest' as DateType

RESIDENT MEDDS;

LOAD [I_MASTER_ID], [D_EST_DATE] as CanonicalDate, 'EstTest' as DateType

RESIDENT MEDDS;

[#6]

swuehl‌

Steven I'm still lost.

Do you still need to create MasterCalendar for CanonicalDate??

Currently, I created MasterCalendar for D_CREATE_DATE & D_EST_RECV_DATE, and then implemented below colde. It is still not working. Please advise.

invitation_id2Date:

     Mapping Load [I_MASTER_ID],[D_CREATE_DATE] Resident MEDDS;

invitation_id2date_accessed:

     Mapping Load [I_MASTER_ID],[D_EST_RECV_DATE] Resident MEDDS;

DateBridge:

LOAD [I_MASTER_ID], [D_CREATE_DATE] as CanonicalDate, 'CreateTest' as DateType

  RESIDENT MEDDS:

LOAD [I_MASTER_ID], [D_EST_RECV_DATE] as CanonicalDate, 'EstTest' as DateType

  RESIDENT MEDDS;

[swuehl]

You don't need the master calendar to be able to use the canonical date as dimension.

That's optional (it allows you to use Month, Year etc. dimensions in addition to the date)

What is the issue that you are facing in detail?

[#8]

swuehl‌

In Bar chart I have, it is only plotting the dates only for D_CREATE_DATE. I'm not able to see D_EST_RECV_DATE dates. I have tried without MasterCalendar, but that is also failing.

Measures:

Blue Measure: Count({$<DateType={'CreateTest'}>}D_CREATE_DATE)

Red Measure: Count({$<DateType={'EstTest'}>}D_Est_Recv_DATE)

DataBridge:

LOAD [I_MASTER_ID],[D_CREATE_DATE] as CanonicalDate, 'CreateTest' as DateType

  RESIDENT Sheet1;

LOAD [I_MASTER_ID], [D_EST_RECV_DATE] as CanonicalDate, 'EstTest' as DateType

  RESIDENT Sheet1;

Excel Data:   

I_MASTER_ID	D_CREATE_DATE	D_EST_RECV_DATE	D_EST_SUB_DATE
111335	1/1/2015	1/3/2015	1/7/2015
111225	1/5/2015	1/9/2015	1/25/2015
111245	1/7/2015	1/8/2015	1/18/2015
111330	1/11/2015	1/20/2015	1/22/2015
111229	1/21/2015	1/25/2015	1/28/2015
111258	1/29/2015	1/30/2015	1/31/2015

[swuehl]

The primary dimension should be your canonical date field, CanonicalDate

[#10]

That Seems to have worked with my sample example.

I will implement the same logic with my main app.

You have been very active in community, and you probably helped thousands like me.

I wanna say thank you for your help.