Data model help question

[#1]

If i have a data model as per below and i need to do join. How would i avoid sythetic key.

Master Customer

Master Account No

Master Account Name

Customer_Info:

Sub account

Master Account No

Name

Contact

Expiree Info Temp:

Sub Account

Year Exp

Month Exp

Day Exp

End result. I need to merge Expiree Info field into Master Customer. In order to do so I have to:

1. Left Join Expiree Info Temp with Customer_info.  Drop table Expiree_info_Temp.
2. Left Join Customer_Info (Expiree Info Temp related Fields) into Master Customer.

However when i do this I will have below fields duplicated on 2 tables creating syc key.

Year Exp

Month Exp

Day Exp 

How can i avoid that. 2 ways i can think of is:

1. Rename field when merging it with Master Customer. however this is dupe of fields.
2. Do a final resident load of Customer_info and drop those fields

I wonder if there is a better way to avoid this.

[#2]

Hi MD,

may you paste your script?

reg

dm

[Anonymous]

Qualify those three fields

[eduardo_sommer]

After the join, you can drop the entire tables (Expiree_info_Temp and Customer_Info), unless you have records in the right tables with no match in the left tables and you need them.

[MayilVahanan]

Hi

After join with master table, drop fields (Year Exp,Month Exp,Day Exp)  from "Customer_Info" table.

So you can remove synthetic key.

[PradeepReddy]

If we are doing the Left Join,  synthatic keys won't be formed.  I have tried to do  what you did.

Master_Customer:

LOAD [Master Account No],
           [Master Account Name]
FROM

Account.xlsx
(ooxml, embedded labels, table is [Master Customer]);

Left Join(Master_Customer)
Customer_Info:
LOAD [Sub account],
     [Master Account No],
     Name,
     Contact
FROM
Account.xlsx
(ooxml, embedded labels, table is Customer_Info);

Left Join(Master_Customer)
Expiree_Info_Temp:
LOAD [Sub Account],
     [Year Exp],
     [Month Exp],
     [Day Exp]
FROM
Account.xlsx
(ooxml, embedded labels, table is [Expiree Info Temp]);

Regards,

Pradeep

[#7]

Hi

and why don't you keepo your model as it is ?

If you want to join 2 tables why do you keep both after ?

best regards

Chris

[#8]

I think you can go with QUALIFY statement...