topic Re: Regarding my Scenario in QlikView

Regarding my Scenario

Thu, 30 Mar 2017 16:35:39 GMT

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

Re: Regarding my Scenario

aarkay29 — Thu, 30 Mar 2017 17:05:38 GMT

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;

Re: Regarding my Scenario

Fri, 31 Mar 2017 15:12:27 GMT

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

Re: Regarding my Scenario

Fri, 31 Mar 2017 15:14:07 GMT

Dear Friends,

Kindly let me know your suggestions!

Thank you!

Re: Regarding my Scenario

aarkay29 — Mon, 03 Apr 2017 12:59:46 GMT

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.

Re: Regarding my Scenario

Kushal_Chawda — Mon, 03 Apr 2017 13:21:18 GMT

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;