Canonical date creation

[#1]

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

[hic]

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

[#3]

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 error

Thanks,

Pramod

[hic]

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

[#5]

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

[hic]

If you load from an internal Qlik Sense table, you need to use "Resident" and not "From" in your load statement.

HIC

[#7]

One more issue.

In the attached image.

you can see in first bar graph the bargraph with blue colour is showing for all the weekdays. but that value is only for monday and tuesday. (check second graph with in weekdays sheet).

Please help on it. I am attaching the .Qvf file.

Thanks,

Pramod