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
Is there any base Sql query on which you are doing this or try to explain what you want to get by giving simple table name like A, B and C
Write your code like this:
Response:
LOAD RID,
Q001,
Q002,
Q003,
Q004,
Q005;
SQL SELECT *
FROM Response;
LOAD QID,
`Option_Value`,
`Option_Text`,
`Is_Excluded`;
SQL SELECT *
FROM `Question_option`;
Response_Final:
CrossTable(QID, Values)
Load *
resident Response;
Drop Table Response;
I think the key is to load all your data from 'campaign' and then left join to the full table, but only to fields with the expiration_date before your variable, so you need to 'flag' that data, something like ...
let varExpirationDate = Timestamp(Today(),'YYYY-MM-DD hh:mm:ss');
MainTable:
LOAD `campaign_id`,
`batch_meta_data_id`,
`campaign_name`,
`start_date`,
`expiration_date`,
if(expiration_date < '$(varExpirationDate)',1,0) as expFlag
Where Match(batch_meta_data_id,'88','23','24');
SQL SELECT `campaign_id`,
`batch_meta_data_id`,
`campaign_name`,
`start_date`,
`expiration_date`
FROM campaign ;
left join (MainTable)
LOAD
MAX(campaign_id) as campaign_id,
1 as expFlag,
batch_meta_data_id as last_batch
Resident MainTable
Group By batch_meta_data_id;
flipside
Assume i have data like below
batch_meta_data_id | campaign_id | campaign_name | start_date | expiration_date |
1 | 2 | as | 02-02-2017 | 23-03-2017 |
1 | 3 | ds | 25-02-2017 | 22-03-2017 |
1 | 4 | fd | 12-02-2017 | 16-03-2017 |
1 | 5 | sa | 12-02-2017 | 18-03-2017 |
1 | 6 | sa | 12-02-2017 | 17-03-2017 |
i want to get only these
batch_meta_data_id | campaign_id | campaign_name | start_date | expiration_date |
1 | 2 | as | 02-02-2017 | 23-03-2017 |
1 | 3 | ds | 25-02-2017 | 22-03-2017 |
1 | 5 | sa | 12-02-2017 | 18-03-2017 |
Here first two are having expiration_date >= today, and third record is the top one record(i.e lastest expired record) where expiration_date < today.
Let me know if you need more clarification
I need to get data like this for all batch_meta_data_id.
Please help me on this.
Hi Kiran,
you can refer example added . Please let me know if you need more inforamtion.
Hi Supriya,
You can try with two temporary tables..
Fill the first table with first condition and fill the second table with second condition.
Then combine/concatenate both the tables.
You will get the output..
Regards,
Rajesh R. S.
HI,
i have done the same thing in my query, check my question. It creates Synthetic Key
Okay, try with this code. It probably needs more testing, though. The 'left join' needed to find the max expiration_date rather than batch_meta_data_id, and the end result moved to a new table to filter out the redundant data.
let varExpirationDate = Timestamp(Today(),'YYYY-MM-DD hh:mm:ss');
MainTable:
Load *, if(expiration_date < '$(varExpirationDate)',1,0) as expFlag inline [
batch_meta_data_id, campaign_id, campaign_name, start_date, expiration_date
1, 2, as, 02-02-2017, 23-03-2017
1, 3, ds, 25-02-2017, 22-03-2017
1, 4, fd, 12-02-2017, 16-03-2017
1, 5, sa, 12-02-2017, 18-03-2017
1, 6, sa, 12-02-2017, 17-03-2017
];
// MainTable:
// LOAD `campaign_id`,
// `batch_meta_data_id`,
// `campaign_name`,
// `start_date`,
// `expiration_date`,
// if(expiration_date < '$(varExpirationDate)',1,0) as expFlag
// Where Match(batch_meta_data_id,'88','23','24');
// SQL SELECT `campaign_id`,
// `batch_meta_data_id`,
// `campaign_name`,
// `start_date`,
// `expiration_date`
// FROM campaign ;
left join (MainTable)
LOAD
MAX(expiration_date) as expiration_date,
'1' as expFlag,
batch_meta_data_id 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,
Yes, this is what i was looking for.
Ah! i found what is causing issue here, its working fine if i use only one batch, if there are multiple batches it shows issue of displaying two latest expired campaign.
Please look into attached qvf