6 Replies Latest reply: Jun 10, 2015 9:42 AM by Kumar Pramod RSS

    Canonical date creation

    Kumar Pramod

      Hi all,

       

      I am facing problem in creation of canonical date.

      I have using the below scripts and i have attached the .qvf file.

       

      LIB CONNECT TO 'MyDB';

      T1:

      LOAD `invitation_id`,

          `client_id`,

          `batch_meta_data_id`,

           activated,

          `date_activated`,

           date((date_created),'DD-MM-YYYY') as Date,

           date#(left(date_created,10),'YYYY-MM-DD') as date_created,

          

           time#(mid(date_created,12,8),'hh:mm:ss') as time_created,

           date(WeekStart(date_created), 'MMM DD') &' - '& date(WeekEnd(date_created), 'MMM DD')  as Week_Group,

           WeekDay(date_created) as week_Day,

           MonthName(date_created) as Month_Year,

           weekstart(date_created) as weekstart,

           year(date_created) as year,

           `campaign_name_id`;

      SQL SELECT `invitation_id`,

          `client_id`,

          `batch_meta_data_id`,

          activated,

          `date_activated`,

          `date_created`,

          `campaign_name_id`

      FROM rcdbrpt.invitation WHERE client_id in (6,12,13,15,16,3,8,10);

       

      LIB CONNECT TO 'MyDB';

      T2:

      LOAD `invitation_id`,

          `user_signed`,

          landing_page_accessed,

          WeekDay(date_signed) as week_Day2,

          date#(left(date_signed,10),'YYYY-MM-DD') as common_date1,

          date(floor(date_signed)) as date_signed;

      SQL SELECT `invitation_id`,

          `user_signed`,

          `date_signed`,

          landing_page_accessed

      FROM rcdbrpt.signupactivitylog WHERE client_id in (6,12,13,15,16,3,8,10);

       

      LIB CONNECT TO 'MyDB';

      T3:

      LOAD `course_completion_id`,

          `invitation_id`,

          `final_test_status`,

          `course_completed`,

           date(floor(date_completed)) as date_completed,

           date#(left(date_completed,10),'YYYY-MM-DD') as common_date2,

           time(frac(date_completed),'hh:mm') as time_completed,

            WeekDay(date_completed) as week_Day1,

           week(date_completed) as week,

           month(date_completed) as month,

           year(date_completed) as year1;

      SQL SELECT `course_completion_id`,

          `invitation_id`,

          `final_test_status`,

          `course_completed`,

            date_completed

      FROM rcdbrpt.coursecompletion;

       

       

      DateBridge:

       

           Load invitation_id, Applymap('invitation_id2Date',invitation_id,Null()) as CanonicalDate, 'invitation' as DateType

       

                Resident T1;

       

           Load invitation_id, Applymap('invitation_id2date_signed',invitation_id,Null()) as CanonicalDate, 'signup' as DateType

       

                Resident T2;

       

           Load invitation_id, date_completed as CanonicalDate, 'completion' as DateType

       

                Resident T3;

       

       

      Anyone help in creation of canonical date.

       

      Thanks,

      Pramod

        • Re: Canonical date creation
          Henric Cronström

          You need to create the Mapping Loads that you use in the Applymap() calls. See Don't join - use Applymap instead.

           

          Otherwise it looks OK.

           

          HIC

            • Re: Canonical date creation
              Kumar Pramod

              Hi Henric Cronstrom,

               

              I used Mapping loads, but i am getting error while loading.

               

              I used below code:

               

              invitation_idtoDate:

                   Mapping Load invitation_id, Date From T1 ;

              invitation_idtodate_signed:

                   Mapping Load invitation_id, date_signed From T2 ;

                  

               

              DateBridge:

               

                   Load invitation_id, Applymap('invitation_idtoDate',invitation_id,Null()) as CanonicalDate, 'invitation' as DateType

               

                        Resident T1;

               

                   Load invitation_id, Applymap('invitation_idtodate_signed',invitation_id,Null()) as CanonicalDate, 'signup' as DateType

               

                        Resident T2;

               

                   Load invitation_id, date_completed as CanonicalDate, 'completion' as DateType

               

                        Resident T3;

               

              please help me. I have added the screenshot of errorerror.png

               

              Thanks,

              Pramod

                • Re: Canonical date creation
                  Henric Cronström

                  Instead of

                      invitation_idtoDate:

                      Mapping Load invitation_id, Date From T1 ;

                      invitation_idtodate_signed:

                      Mapping Load invitation_id, date_signed From T2 ;

                   

                  you should use

                      invitation_id2Date:

                      Mapping Load distinct invitation_id, Date Resident T1 ;

                      invitation_id2date_signed:

                      Mapping Load distinct invitation_id, date_signed Resident T2 ;

                   

                  HIC

                    • Re: Canonical date creation
                      Kumar Pramod

                      Hi Henric Cronstrom,

                       

                      In the below code i am excluding use of  T1, T2, T3. and tried to load directly using the table names( invitation,  signupactivitylog and coursecompletion )

                      But i am getting the same error.

                       

                      Can you please check and tell me where i am making wrong?

                       

                      LIB CONNECT TO 'MyDB';

                       

                      LOAD `invitation_id`,

                          `client_id`,

                          `batch_meta_data_id`,

                           activated,

                          `date_activated`,

                           date((date_created),'DD-MM-YYYY') as Date,

                           date#(left(date_created,10),'YYYY-MM-DD') as date_created,

                          

                           time#(mid(date_created,12,8),'hh:mm:ss') as time_created,

                           date(WeekStart(date_created), 'MMM DD') &' - '& date(WeekEnd(date_created), 'MMM DD')  as Week_Group,

                           WeekDay(date_created) as week_Day,

                           MonthName(date_created) as Month_Year,

                           weekstart(date_created) as weekstart,

                           year(date_created) as year,

                           `campaign_name_id`;

                      SQL SELECT `invitation_id`,

                          `client_id`,

                          `batch_meta_data_id`,

                          activated,

                          `date_activated`,

                          `date_created`,

                          `campaign_name_id`

                      FROM rcdbrpt.invitation WHERE client_id in (6,12,13,15,16,3,8,10);

                       

                      LIB CONNECT TO 'MyDB';

                      LOAD `invitation_id`,

                          `user_signed`,

                          landing_page_accessed,

                          WeekDay(date_signed) as week_Day2,

                          date#(left(date_signed,10),'YYYY-MM-DD') as common_date1,

                          date(floor(date_signed)) as date_signed;

                      SQL SELECT `invitation_id`,

                          `user_signed`,

                          `date_signed`,

                          landing_page_accessed

                      FROM rcdbrpt.signupactivitylog WHERE client_id in (6,12,13,15,16,3,8,10);

                       

                      LIB CONNECT TO 'MyDB';

                       

                      LOAD `course_completion_id`,

                          `invitation_id`,

                          `final_test_status`,

                          `course_completed`,

                           date(floor(date_completed)) as date_completed,

                           date#(left(date_completed,10),'YYYY-MM-DD') as common_date2,

                           time(frac(date_completed),'hh:mm') as time_completed,

                            WeekDay(date_completed) as week_Day1,

                           week(date_completed) as week,

                           month(date_completed) as month,

                           year(date_completed) as year1;

                      SQL SELECT `course_completion_id`,

                          `invitation_id`,

                          `final_test_status`,

                          `course_completed`,

                            date_completed

                      FROM rcdbrpt.coursecompletion;

                       

                      invitation_id2Date:

                           Mapping Load invitation_id, Date From rcdbrpt.invitation ;

                      invitation_id2date_signed:

                           Mapping Load invitation_id, date_signed From rcdbrpt.signupactivitylog ;

                          

                       

                      DateBridge:

                       

                           Load invitation_id, Applymap('invitation_id2Date',invitation_id,Null()) as CanonicalDate, 'invitation' as DateType

                       

                                FROM rcdbrpt.invitation;

                       

                           Load invitation_id, Applymap('invitation_id2date_signed',invitation_id,Null()) as CanonicalDate, 'signup' as DateType

                       

                                FROM rcdbrpt.signupactivitylog;

                       

                           Load invitation_id, date_completed as CanonicalDate, 'completion' as DateType

                       

                                FROM rcdbrpt.coursecompletion;

                       

                       

                       

                       

                      Thanks,

                      Pramod