Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Canonical date creation

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

Tags (1)
6 Replies
Employee
Employee

Re: Canonical date creation

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

Not applicable

Re: Canonical date creation

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

Employee
Employee

Re: Canonical date creation

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

Not applicable

Re: Canonical date creation

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

Employee
Employee

Re: Canonical date creation

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

HIC

Not applicable

Re: Canonical date creation

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.

count.png

Thanks,

Pramod