Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Friends,
I appreciate your time! I have my scenario as follows, kindly share the best way to design my data model.
My table with field details below:
Sales Table : CustomerNumber,Customer,Customer Kind,DivisionNumber,Territory,Sales,Date,SalesPerson
Customer Table : CustomerNumber,Customer,Customer Kind,DivisionNumber,Territory,Business,Phone
Division Lookup Table : Division Number,Division Name
Territory Lookup Table : Territory,Territory Name.
If the field values of the tables Customer,Customer Type,Division,Region are same in both Sales and Customer table. What is the best way to build my data model as I need to calculate the sales based on SalesPerson. Kindly share your ideas.
I have removed CustomerType field in Sales Table, DivisionNumber and Territory Fields in Sales Table to avoid synthetic key.
Is that ok?
Thank you,
Jeevi
I believe the best approach here is to make a composite key by concatenating Customer,Customer Kind,DivisionNumber,Territory
Sales:
Load
Customer&Customer Kind&DivisionNumber&Territory as %Key,
Customer,Customer Kind,DivisionNumber,Territory,Sales,Date,SalesPerson
From Sales;
Customer
Load
Customer&Customer Kind&DivisionNumber&Territory as %Key,
Business,Phone
From Customer;
Division:
Load
Division Number,Division Name
From Division;
Territory:
Load
Territory,Territory Name
From territory;
Hello Aar Kay,
I appreciate your time. I actually have CustomerNumber field along with Customer,Customer Kind,DivisionNumber,Territory fields in both sales and customer table. So I am using that to connect between these tables.
IS that ok,kindly let me know!
Thank you!
Jeevi
Dear Friends,
Kindly let me know your suggestions!
Thank you!
If Cutomer Number is a unique number with each Number associated with unique/only one Customer field then you can just link by Customer Number else use the composite key by concatenating the fields!!!
Hope it helps
Thanks,
Aar.
try this
Use autonumber on key as it is composite key
Sales:
Load
Autonumber(CustomerNumber & Customer Kind & DivisionNumber &Territory) as Key,
Sales,Date,SalesPerson
From Sales;
Customer:
Load
Autonumber(CustomerNumber & Customer Kind & DivisionNumber &Territory) as Key,
Customer,CustomerNumber,Customer Kind,DivisionNumber,Territory,Business,Phone
From Customer;
left join(Customer)
Load
Division Number,Division Name
From Division;
left join(Customer)
Load
Territory,Territory Name
From territory;