12 Replies Latest reply: Jun 13, 2017 11:31 AM by Dushyant Patel RSS

    Canonical Dates for 1 table

    Dushyant Patel

      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

        • Re: Canonical Dates for 1 table
          Stefan Wühl

          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.

            • Re: Canonical Dates for 1 table
              Dushyant Patel

              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 ;  

                • Re: Canonical Dates for 1 table
                  Stefan Wühl

                  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;

                    • Re: Canonical Dates for 1 table
                      Dushyant Patel

                      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;

                  • Re: Canonical Dates for 1 table
                    Dushyant Patel

                    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