Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Hi Dave,
thank you so much for sharing your idea and time. Your solution works is very helpful.