Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
HI,
i have below code
LOAD
campaign_id
RESIDENT CAMPAIGN where campaign_start = Date(Num(Max(campaign_start, 2))) and match(camp_batch_meta_data_id ,24);
here i am trying to retrieve 'campaign_id ' where camp_batch_meta_data_id = 24 and campaign_start date will be 2nd largest in table.
If i load this i am getting Unknown error.
What i am doing wrong. Please help me on this
Update: Please check Sunny's answers. This can be done both in load editor and front end as per requirement. Both works fine.
I see you renamed the Max(campaign_redirect_start, 2)
Try this:
Table:
LOAD campaign_id as max_campaign_start,
campaign_redirect_start
Resident CAMPAIGN
Where Match(camp_batch_meta_data_id ,24);
Right Join (Table)
LOAD Max(campaign_redirect_start, 2) as max_campaign_redirect_start campaign_redirect_start
Resident Table;
So instead of using max_campaign_redirect_start use campaign_redirect_start
You are not allowed to use Aggregation functions such as Max in the Where Statement. You might want to do something like this:
Table:
LOAD campaign_id,
campaign_start
Resident CAMPAIGN
Where Match(camp_batch_meta_data_id ,24);
Right Join (Table)
LOAD Max(campaign_start, 2) as campaign_start
Resident Table;
Hi Sunny,
Thanks for reply
Now it is not showing any error, but it shows all campaign_id of camp_batch_meta_data_id = 24
Basically, what i am trying is to get latest 2nd campaign_id in batch 24, for that
First i filter CAMPAIGN table with camp_batch_meta_data_id = 24 and then in that(filtered table), i check for campaign_start, 2nd highest date to get 2nd latest date using Max(campaign_start, 2) .
But it showing all campaign_id for batch 24
Can you share the exact script you are using? Did you use Right Join?
HI,
LOAD `campaign_id`,
`batch_meta_data_id` as camp_batch_meta_data_id,
`campaign_id` as campaign_name_redirect,
`start_date` as campaign_redirect_start,
`campaign_name`;
SQL SELECT `campaign_id`,
`start_date`,
`batch_meta_data_id`,
`campaign_name`
FROM CAMPAIGN;
Table:
LOAD campaign_id as max_campaign_start,
campaign_redirect_start
Resident CAMPAIGN
Where Match(camp_batch_meta_data_id ,24);
Right Join (Table)
LOAD Max(campaign_redirect_start, 2) as max_campaign_redirect_start
Resident Table;
I am exactly using as above,
I see you renamed the Max(campaign_redirect_start, 2)
Try this:
Table:
LOAD campaign_id as max_campaign_start,
campaign_redirect_start
Resident CAMPAIGN
Where Match(camp_batch_meta_data_id ,24);
Right Join (Table)
LOAD Max(campaign_redirect_start, 2) as max_campaign_redirect_start campaign_redirect_start
Resident Table;
So instead of using max_campaign_redirect_start use campaign_redirect_start
Thanks, It worked
Hi,
one more thing, instead of using in script, can i add this condition in front end?
If so, how could i do that?
You will need to provide more details for me to give you an answer for this. You are wanting to see this in a chart? What are your dimensions and what are your expressions?
HI Sunny,
Here i need to get counts of landing hits according to campaigns,batches and latest date of batch(to get first and second largest campaign) and update in table.
Here i am just using expression to get counts/sum and displaying in table
currently i am using
sum({$<batch_meta_data_id={'42''}>} landing_hits)
Now i need to apply condition mentioned in script, to expression in front end
How could i apply script condition in front end expression?
I could provide more information, if my provided information is not clear
Regards,
Supriya