Combining two tables data into single table

[berryandcherry6]

Hi,

i am trying to append/combine  both table data and put that to one single table using below code

let varExpirationDate  = Timestamp(Today(),'YYYY-MM-DD hh:mm:ss');

Welcome:

LOAD `campaign_id`,

    `batch_meta_data_id`,

    `campaign_name`,

    `start_date`,

    `expiration_date` Where Match(batch_meta_data_id,'88','23','24') and expiration_date < '$(varExpirationDate)';

SQL SELECT `campaign_id`,

    `batch_meta_data_id`,

    `campaign_name`,

    `start_date`,

    `expiration_date`

FROM campaign ;

NoConcatenate

MainTable:

LOAD

batch_meta_data_id as last_batch,

MAX(campaign_id) as campaign_id

Resident Welcome  Group By batch_meta_data_id Order By batch_meta_data_id;

Left Join

LOAD  campaign_id,

batch_meta_data_id,

`campaign_name`,

`start_date`,

`expiration_date`

Resident Welcome;

Drop Table Welcome;

Welcome1:

LOAD `campaign_id`,

    `batch_meta_data_id` ,

    `campaign_name` ,

    `start_date` ,

    `expiration_date` Where Match(batch_meta_data_id,'88','23','24') and expiration_date >= '$(varExpirationDate)';

SQL SELECT `campaign_id`,

    `batch_meta_data_id`,

    `campaign_name`,

    `start_date`,

    `expiration_date`

FROM campaign;

These scripts create synthetic table. How to avoid this and get one single table?

Is there any other way i could achieve same with less code?

Please help me on this. Any suggestion and idea is appreciated.

[flipside · Accepted solution]

Try changing the GROUP BY code to include the batch_meta_data_id, which means that rather than just 'left-joining' by expiration_date, you now join by expiration_date AND batch_meta_data_id ...

left join (MainTable)
LOAD
MAX(expiration_date) as expiration_date,
'1' as expFlag,
batch_meta_data_id,
1 as last_batch
Resident MainTable
where expFlag = 1
Group By batch_meta_data_id;

EndTable:
Noconcatenate Load * resident MainTable where last_batch = 1 OR expFlag = 0;

Drop Table MainTable;

flipside

[berryandcherry6]

Hi Dave,

thank you so much for sharing your idea and time. Your solution works is very helpful.