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

join two tables based on criteria in script

Hi all,

i have two tables from which i need to create fact table like below. In table 2 if i found comm_type_id rows that are not in table 2, those records should be appended to table1.

Table1:

batch_idcampaign_idcomm_type_idsent
2334356
23344566
233455
2335634
233592

Table2:

 

batch_idcampaign_idcomm_type_idsent
233463
2334834
233562
233573
233594
2335114
2335105
233637
233648
2336545

FactTable:

batch_idcampaign_idcomm_type_idsentnot required just reference
2334356T1
23344566T1
233455T1
233463T1
2334834T1
2335634T1
233592T1
233573T2
2335114T2
2335105T2
233637T2
233648T2
2336545T2

if they are multiple records for same comm_type_id then record in table 1 should only be included in facttable.

how could i do this?

1 Solution

Accepted Solutions
kenphamvn
Not applicable

Re: join two tables based on criteria in script

Try This


Table1:

Load * ...from Source1;


noConcatenate

Table2:

Load * from Source2

TBLTemp:

Load batch_id&campaign_id&comm_type_id as Excludelist

From Table1;


FactTable:

Load

batch_id,campaign_id,comm_type_id,sent,'T1' as TableRef

resident Table1;

Concatenate

Load

batch_id,campaign_id,comm_type_id,sent,'T2' as TableRef

resident Table2

where not exists(Excludelist,batch_id&campaign_id&comm_type_id);


Drop tables TBLTemp,Table1,Table2;

5 Replies
its_anandrjs
Not applicable

Re: join two tables based on criteria in script

Simple try concatenate this table in your data model

Tabl1:

Load

batch_id,campaign_id,comm_type_id,sent,'T1' as TableRef

From Source;

Concatenate(Tabl1)

batch_id,campaign_id,comm_type_id,sent,'T2' as TableRef

From Source;

berryandcherry6
Not applicable

Re: join two tables based on criteria in script

Hi Anand,

but here, if row if same comm_type_id appears in both table, i want to get row of table 1 only not table 2.

table1:

2335634

table2:

233562

want to get table1 row in fact table;

The given code give duplicate rows.

kenphamvn
Not applicable

Re: join two tables based on criteria in script

Try This


Table1:

Load * ...from Source1;


noConcatenate

Table2:

Load * from Source2

TBLTemp:

Load batch_id&campaign_id&comm_type_id as Excludelist

From Table1;


FactTable:

Load

batch_id,campaign_id,comm_type_id,sent,'T1' as TableRef

resident Table1;

Concatenate

Load

batch_id,campaign_id,comm_type_id,sent,'T2' as TableRef

resident Table2

where not exists(Excludelist,batch_id&campaign_id&comm_type_id);


Drop tables TBLTemp,Table1,Table2;

its_anandrjs
Not applicable

Re: join two tables based on criteria in script

Then you have to try this

Tabl1:

Load

batch_id,campaign_id,comm_type_id,sent,'T1' as TableRef

From Source;

Left Join(Tabl1)

batch_id,campaign_id,comm_type_id,sent,'T2' as TableRef

From Source;


OR else create the Link Tables in between this two tables.

gladi-cz
Not applicable

Re: join two tables based on criteria in script

Hi Supriya,

I added solution to .qvf. I created keys between compaign_id and comm_type_id. After i conncatenated table 2 to table 1 with where not exists conditions.

Check it please and contact me if you would like to know something.

Have nice day,

Petr