Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I am trying to create table in script to get client id according to below condition but it showing me error while loading.
Client_IdData:
Load
campaign_id Where campaign_start_date < today() and campaign_start_date > (today()-7) Resident INVITATION;
Please help me in this regard.
What is the error? And since you are using RESIDENT LOAD ensure the "INVITATION" table is loaded first and PUT the WHERE condition after the "RESIDENT load"
Client_IdData:
Load
campaign_id Resident INVITATION
Where campaign_start_date < today() and campaign_start_date > (today()-7);
The above code is for representation. Not directly tested.
hI
Try this
RESIDENT INVITATION WHERE campaign_start_date < today() and campaign_start_date > (today()-7) ;
But i am not sure , whether above statement gives last one week data !!!
Hi Supriya
You need to move the Resident command to be just after the load statement, please see below:
Client_IdData:
Load
campaign_id
Resident INVITATION;
Where
campaign_start_date < today()
and
campaign_start_date > (today()-7);
Client_IdData:
Load
campaign_id
Resident INVITATION Where (campaign_start_date < today() and campaign_start_date > (today()-7) );
where should be always after resident
now i am getting field campaign_start_date not found. because campaign_start_date is from different table i.e CAMPAIGNMETRICS and campaign_id is from INVITATION table.How could i acheive this.
Can you please check whether campaign_start_date is available in INVITATION table?
Hi Sunil,
campaign_start_date is not in Invitation Table,its in CAMPAIGNMETRICS Table.
but why you are taking resident from INVITATION . I dont know exact scnario but i think there might be two thimgs below Can be done
1) either Take Resident from CAMPAIGNMETRICS
or Combine INVITATION and CAMPAIGNMETRICS based on some key field and then take resident
its better you if you describe full Scanario
Hi sunil,
here i am trying to get campaign_id from invitation table where campaign_start_date is less than today and greater than today-7.
Both the table have already connected
LOAD `invitation_id`,
`client_id`,
`batch_meta_data_id`,
activated,
date(`date_activated`,'MM-DD-YYYY') as date_activated,
language_id,
date_created as date_created_timestamp,
`campaign_id`;
SQL SELECT `invitation_id`,
`client_id`,
`batch_meta_data_id`,
activated,
`date_activated`,
`date_created`,
`campaign_id`,
language_id
FROM INVITATION
LOAD `batch_meta_data_id`,
`campaign_metrics_id`,
`landing_page_hits`,
`sign_up`,
completions,
fails,
partials,
num_required_subjects;
SQL SELECT
`batch_meta_data_id`,
`campaign_metrics_id`,
`landing_page_hits`,
`sign_up`,
completions,
fails,
partials,
num_required_subjects
FROM CAMPAIGNMETRICS
so is this enough to take resident of invitation. or both table resident.