Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Assume i have a data like below
Batch_id | Campaign_id | Campaign_name |
55 | 52 | camp_bc |
55 | 53 | camp_cd |
55 | 51 | camp_ab |
56 | 62 | camp_bc |
57 | 71 | camp_ab |
55 | 54 | camp_ef |
58 | 81 | camp_ab |
56 | 61 | camp_ab |
54 | 91 | camp_ab |
57 | 22 | camp_bc |
I want to create a script to order batch_id in Ascending , campaign_id in Descending, and to create a one more column called brand_name with latest campaign_id with brand_1, second latest as brand_2 etc.. as shown in below figure
Batch_id | Campaign_id | Campaign_name | brand_name |
54 | 91 | camp_ab | brand_1 |
55 | 54 | camp_ef | brand_4 |
55 | 53 | camp_cd | brand_3 |
55 | 52 | camp_bc | brand_2 |
55 | 51 | camp_ab | brand_1 |
56 | 62 | camp_bc | brand_2 |
56 | 61 | camp_ab | brand_1 |
57 | 72 | camp_bc | brand_2 |
57 | 71 | camp_ab | brand_1 |
58 | 81 | camp_ab | brand_1 |
I have below code currently
let varExpirationDate = Timestamp(Today(),'YYYY-MM-DD hh:mm:ss');
LOAD `campaign_id`,
`campaign_group_id`,
`batch_meta_data_id`,
`start_date` as Campaign_Start_Date,
date(floor(expiration_date),'MM-DD-YYYY') as expiration_date,
`campaign_name` where expiration_date >= '$(varExpirationDate)';
SQL SELECT `campaign_id`,
`start_date`,
`batch_meta_data_id`,
`campaign_group_id`,
`campaign_name`,
expiration_date
FROM CAMPAIGN where batch_meta_data_id IN ('54','55','56','57','58') Order by campaign_id desc;
May be this:
let varExpirationDate = Timestamp(Today(),'YYYY-MM-DD hh:mm:ss');
Table:
LOAD `campaign_id`,
`campaign_group_id`,
`batch_meta_data_id`,
`start_date` as Campaign_Start_Date,
date(floor(expiration_date),'MM-DD-YYYY') as expiration_date,
`campaign_name` where expiration_date >= '$(varExpirationDate)';
SQL SELECT `campaign_id`,
`start_date`,
`batch_meta_data_id`,
`campaign_group_id`,
`campaign_name`,
expiration_date
FROM CAMPAIGN where batch_meta_data_id IN ('54','55','56','57','58') Order by campaign_id desc;
FinalTable:
LOAD *,
'brand_' & AutoNumber(Campaign_id, batch_meta_data_id) as brand_name
Resident Table
Order By batch_meta_data_id, Campaign_id;
DROP Table Table;
May be this:
let varExpirationDate = Timestamp(Today(),'YYYY-MM-DD hh:mm:ss');
Table:
LOAD `campaign_id`,
`campaign_group_id`,
`batch_meta_data_id`,
`start_date` as Campaign_Start_Date,
date(floor(expiration_date),'MM-DD-YYYY') as expiration_date,
`campaign_name` where expiration_date >= '$(varExpirationDate)';
SQL SELECT `campaign_id`,
`start_date`,
`batch_meta_data_id`,
`campaign_group_id`,
`campaign_name`,
expiration_date
FROM CAMPAIGN where batch_meta_data_id IN ('54','55','56','57','58') Order by campaign_id desc;
FinalTable:
LOAD *,
'brand_' & AutoNumber(Campaign_id, batch_meta_data_id) as brand_name
Resident Table
Order By batch_meta_data_id, Campaign_id;
DROP Table Table;
Hi Sunny,
i am getting sync error.
Did you forget to drop the Table?
Thanks Sunny, that was quick needed response.
Hi Sunny,
one more thing,
Here instead of batch_1, batch 2 ...., i need to keep it as current week, latest week, second latest week.
How could i do that. But it has to be different words for diferent brand_name
Can you explain by amending your above example?
Because in our services, we usually show data for campaigns. Some times these campaigns will be running for 1 week, 2 week, 3 week ... so at this time i need to display them whether they are Current Week, Previous Week or Last Week.
That's not what I asked for.... You shared this in your initial post
I guess you want to change brand_name to something else now? Is that right?