Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
Not applicable

Canonical Dates for 1 table

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Canonical Dates for 1 table

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;

12 Replies
MVP
MVP

Re: Canonical Dates for 1 table

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.

Not applicable

Re: Canonical Dates for 1 table

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 ;  

Not applicable

Re: Canonical Dates for 1 table

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_IDD_CREATE_DATED_EST_RECV_DATED_EST_SUB_DATE
1114501/15/20141/30/20142/14/2014
1113251/30/20142/14/20143/1/2014
1113205/5/20145/20/20146/4/2014
1113303/5/20163/20/20164/4/2016
1113403/10/20173/25/20174/9/2017
1113501/15/20141/30/20142/14/2014
1113601/30/20142/14/20143/1/2014
1113705/5/20145/20/20146/4/2014

Please suggest some ideas.

Thanks

MVP
MVP

Re: Canonical Dates for 1 table

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;

Not applicable

Re: Canonical Dates for 1 table

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;

MVP
MVP

Re: Canonical Dates for 1 table

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?

Not applicable

Re: Canonical Dates for 1 table

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;

9.PNG

Excel Data:   

I_MASTER_IDD_CREATE_DATED_EST_RECV_DATED_EST_SUB_DATE
1113351/1/20151/3/20151/7/2015
1112251/5/20151/9/20151/25/2015
1112451/7/20151/8/20151/18/2015
1113301/11/20151/20/20151/22/2015
1112291/21/20151/25/20151/28/2015
1112581/29/20151/30/20151/31/2015
MVP
MVP

Re: Canonical Dates for 1 table

The primary dimension should be your canonical date field, CanonicalDate

Not applicable

Re: Canonical Dates for 1 table

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.