Hi Sinan,
Thanks for the reply.
Sorry In the script i have some more tables i have not mentioned it.
Can you see my script, I have added complete script.
LOAD `invitation_id`,
`client_id`,
`batch_meta_data_id`,
activated,
`date_activated`,
date(floor(date_created),'MM-DD-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,
week(date_created) as week_number,
MonthName(date_created) as Month_Year,
weekstart(date_created) as weekstart,
WeekDay(date_created) as WeekDay,
`campaign_name_id`;
SQL SELECT `invitation_id`,
`client_id`,
`batch_meta_data_id`,
activated,
`date_activated`,
`date_created`,
`campaign_name_id`
FROM rcdbprod.INVITATION WHERE client_id in (6,12,13,15,16,3,8,10,18);
LOAD `client_id`,
`client_name`;
SQL SELECT `client_id`,
`client_name`
FROM rcdbprod.CLIENT WHERE client_id in (6,12,13,15,16,3,8,10,18);
LOAD `batch_meta_data_id`,
date((start_date),'MM-DD-YYYY') as start_date,
`batch_name`;
SQL SELECT `batch_meta_data_id`,
`batch_name`,
start_date
FROM rcdbprod.BATCHMETADATA WHERE client_id in (6,12,13,15,16,3,8,10,18);
LOAD `course_completion_id`,
`invitation_id`,
`lender_offer_id`,
`final_test_status`,
percentage_score,
`course_completed`,
date(floor(date_completed),'MM-DD-YYYY') as date_completed;
SQL SELECT `course_completion_id`,
`invitation_id`,
`lender_offer_id`,
`final_test_status`,
`course_completed`,
percentage_score,
`date_completed`
FROM rcdbprod.COURSECOMPLETION;
LOAD `invitation_id`,
`user_signed`,
landing_page_accessed,
referrer_email_type_id as email_type_id,
date(floor(date_accessed),'MM-DD-YYYY') as date_accessed,
date(floor(date_signed),'MM-DD-YYYY') as date_signed;
SQL SELECT `invitation_id`,
referrer_email_type_id,
`user_signed`,
`date_signed`,
date_accessed,
landing_page_accessed
FROM rcdbprod.SIGNUPACTIVITYLOG WHERE client_id in (6,12,13,15,16,3,8,10,18);
Concatenate
LOAD
client_id,
status as email_type_id ;
SQL SELECT
client_id,
status
FROM rcdbprod.USERACTIVITYLOG where status in (2,3,4,8);
LOAD `batch_meta_data_id`,
`campaign_metrics_id`,
date(floor(campaign_start_date),'MM-DD-YYYY') as campaign_start_date,
date(floor(campaign_end_date),'MM-DD-YYYY') as campaign_end_date,
`landing_page_hits`,
`sign_up`,
completions,
fails,
partials,
num_required_subjects;
SQL SELECT
`batch_meta_data_id`,
`campaign_metrics_id`,
`campaign_start_date`,
`campaign_end_date`,
`landing_page_hits`,
`sign_up`,
completions,
fails,
partials,
num_required_subjects
FROM rcdbprod.CAMPAIGNMETRICS WHERE client_id in (6,12,13,15,16,3,8,10,18);
invitation_id2Date:
Mapping Load invitation_id, Date Resident INVITATION ;
invitation_id2date_accessed:
Mapping Load invitation_id, date_accessed Resident SIGNUPACTIVITYLOG ;
DateBridge:
Load invitation_id, Applymap('invitation_id2Date',invitation_id,Null()) as CanonicalDate, 'invitation' as DateType
Resident INVITATION;
Load invitation_id, Applymap('invitation_id2date_accessed',invitation_id,Null()) as CanonicalDate, 'accessed' as DateType
Resident SIGNUPACTIVITYLOG ;
Load invitation_id, date_completed as CanonicalDate, 'completion' as DateType
Resident COURSECOMPLETION;
LOAD *,
If(total_time_taken<='00:01:00' ,Dual('00:00-01:00',0),
If(total_time_taken>'00:60:00',Dual('>60:00',60),
Dual(Num(Floor(total_time_taken*1440), '00') & ':00-' & Num(Floor(total_time_taken*1440+1), '00'), Floor(total_time_taken*1440))))
as time_interval;
LOAD `subject_id`,
`subject_name`,
`lender_offer_id`,
`subject_status`,
version,
last_accessed_page,
SubField(If(Match(version, 'v2'), last_accessed_page), ',', -1) as v2,
Interval(Alt(
Interval#(total_time_taken,'hh:mm:ss.ff'),
Interval#(total_time_taken,'hh:mm:ss')
),'hh:mm:ss.fff') as total_time_taken,
user_id,
date((`subject_completed_date`),'MM-DD-YYYY') as `subject_completed_date`;
SQL SELECT `subject_id`,
`subject_name`,
`lender_offer_id`,
`subject_status`,
last_accessed_page,
version,
total_time_taken,
user_id,
`subject_completed_date`
FROM rcdbprod.SUBJECTACCESSTRACKING;
LOAD [question_id],
'Q' & question_id AS Q_id,
'Q' & If(fmod(question_id , 10) = 0, 10, fmod(question_id , 10)) AS QNoMod,
[is_correct_answer],
[lender_offer_id],
[test_type];
SQL SELECT `question_id`,
`is_correct_answer`,
`lender_offer_id`,
`test_type`
FROM `rcdbprod`.`USERQUESTION`;
LOAD `batch_meta_data_id`,
browser,
`Type` as browser_type,
`visits` as browser_visits;
SQL SELECT `batch_meta_data_id`,
browser,
`Type`,
`visits`
FROM rcdbprod.BROWSERSTATS;
LOAD `batch_meta_data_id`,
`operating_system`,
`Type` as os_type,
visits as os_visits;
SQL SELECT `batch_meta_data_id`,
`operating_system`,
`Type`,
visits
FROM rcdbprod.OPERATINGSYSTEMSTATS;
LOAD `email_type_id`,
`email_description`;
SQL SELECT `email_type_id`,
`email_description`
FROM rcdbprod.EMAILTYPES where email_type_id in (2,3,4,8,44,45,46,47,126);
LOAD `invitation_id`,
`invitation_dispatch_channel_id`,
`dispatched_by`;
SQL SELECT `invitation_id`,
`invitation_dispatch_channel_id`,
`dispatched_by`
FROM rcdbprod.INVITATIONACTIVITYLOG;
Regards,
Pramod
