Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
If you load from an internal Qlik Sense table, you need to use "Resident" and not "From" in your load statement.
HIC
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