Loading table with appending extra column and Applying Condition

[berryandcherry6]

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;

[sunny_talwar · Accepted solution]

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;

[berryandcherry6]

Hi Sunny,

i am getting sync error.

[sunny_talwar]

Did you forget to drop the Table?

[berryandcherry6]

Thanks Sunny, that was quick needed response.

[berryandcherry6]

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

[sunny_talwar]

Can you explain by amending your above example?

[berryandcherry6]

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.

[sunny_talwar]

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?