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.

[kkkumar82]

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

[sarvesh]

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;

[flipside]

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

[berryandcherry6]

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.

[berryandcherry6]

Hi Kiran,

you can refer example added . Please let me know if you need more inforamtion.

[#7]

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.

[berryandcherry6]

HI,

i have done the same thing in my query, check my question. It creates Synthetic Key

[flipside]

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

[berryandcherry6]

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