Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
berryandcherry6
Not applicable

Combining two tables data into single table

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.

1 Solution

Accepted Solutions
flipside
Not applicable

Re: Combining two tables data into single table

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

11 Replies
kkkumar82
Not applicable

Re: Combining two tables data into single table

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
Not applicable

Re: Combining two tables data into single table

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
Not applicable

Re: Combining two tables data into single table

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
Not applicable

Re: Combining two tables data into single table

Assume i have data like below

     

batch_meta_data_idcampaign_idcampaign_namestart_dateexpiration_date
12as02-02-201723-03-2017
13ds25-02-201722-03-2017
14fd12-02-201716-03-2017
15sa12-02-201718-03-2017
16sa12-02-201717-03-2017

i want to get only these

batch_meta_data_idcampaign_idcampaign_namestart_dateexpiration_date
12as02-02-201723-03-2017
13ds25-02-201722-03-2017
15sa12-02-201718-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
Not applicable

Re: Combining two tables data into single table

Hi Kiran,

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

Not applicable

Re: Combining two tables data into single table

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
Not applicable

Re: Combining two tables data into single table

HI,

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

flipside
Not applicable

Re: Combining two tables data into single table

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
Not applicable

Re: Combining two tables data into single table

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