join two tables based on criteria in script

[berryandcherry6]

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_id	campaign_id	comm_type_id	sent
23	34	3	56
23	34	4	566
23	34	5	5
23	35	6	34
23	35	9	2

Table2:

batch_id	campaign_id	comm_type_id	sent
23	34	6	3
23	34	8	34
23	35	6	2
23	35	7	3
23	35	9	4
23	35	11	4
23	35	10	5
23	36	3	7
23	36	4	8
23	36	5	45

FactTable:

batch_id	campaign_id	comm_type_id	sent	not required just reference
23	34	3	56	T1
23	34	4	566	T1
23	34	5	5	T1
23	34	6	3	T1
23	34	8	34	T1
23	35	6	34	T1
23	35	9	2	T1
23	35	7	3	T2
23	35	11	4	T2
23	35	10	5	T2
23	36	3	7	T2
23	36	4	8	T2
23	36	5	45	T2

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?

[its_anandrjs]

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]

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:

23

35

6

34

table2:

23

35

6

2

want to get table1 row in fact table;

The given code give duplicate rows.

[kenphamvn · Accepted solution]

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]

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]

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