Qlik Datamodel question

[Anonymous]

I am a new to Qlik. I am trying to design data model in qlik but it is generating a synthetic key.I am unable to figure out how to avoid synthetic key which is created on J_CUST_ID+J_REP_ID.

below are database tables

I am having issue with Cust Rep Table it is a relationship table.Purpose of this table is to get a list of customers assigned to rep regardless of sales.

Please let me know if you have any questions. Thank you in advance.

[vvira1316]

Hi

I would create a concate field using J_CUST_ID and J_REP_ID in Sales Table and Cust Rep Table. Then either remove those two fields from either one Table or rename it (if you will need it), wlill keep those two fields one of the Tables to have lineage to Customer and Rep Tables.

I hope it helps.

[luismadriz]

Hi,

I think I would combine Sales Table and Cust Rep Table into one table and add a descriptor field to the resulting table that will have two values: 'Sales' and 'Assignments'.

The fields Sales and Units in the resulting table will be empty for when the new descriptor field value is 'Assignments' and populated for the value is 'Sales'

Just a thought,

Cheers,

Luis

[luismadriz]

And by combine I mean concatenate one after the other

Cheers,

Luis

[Mark_Little]

Hi

I am assuming from the description that the Combination of customer and reps on the sales table can and will be different to the combinations on the Cust rep table.

In this case I would alias the J_CUST_ID as Cust_With_rep. This would get rid of the synthetic key, but you will still see the Customers under the Rep, if needed you can then map any other details from the customer table to the Customer table, making sure to alias as well.

Mark

[Anonymous]

How about doing Mapping Loads with your Customer & Rep tables, followed by ApplyMap() 's to load the Customer & Rep Names into the Sales Table ?

This should result in an efficient single table data model.

Have a look at this Blog Post by HIC Don't join - use Applymap instead

[Anonymous]

Further to that to get the Customers  /  Reps with no Sales your can concatenate them onto the end of the single Fact table using a Where Not Exists on the Customer & Rep fields leaving all such rows with null values for the actual sales data fields.

[snehalameya]

Hi,

Using Applymap function must solve your problem.

Regards,

Snehal Nabar

[Anonymous]

Thank you all for your responses. By reading all of your responses,i understood how to design datamodel in qlik to achieve my requirement.

However i design my data model,in the end in User Interface users would like to see only Customer, Rep, Sales & Units.

scenario 1.User would like to see Rep and associated customers regardless of sales

scenario 2.User would like to see Rep,customers, sales & units

Since qlik association model is so good, based your responses concatenating cust rep table and sales table i.e loading all customers from cust rep into sales table & by removing duplicate customers works.

but i am having issues removing duplicates.below is my code

[Sales]:

LOAD

    J_REP_ID,

    J_CUST_ID,

    J_SALES,

    J_UNITS

FROM [lib://Qlik_Practice/Qlik Practice1\Sales Table.xlsx]

(ooxml, embedded labels, table is Sheet1);

Concatenate

[Cust Rep]:

Load   J_REP_ID,

   J_CUST_ID

FROM [lib://Qlik_Practice/Qlik Practice1\Cust Rep.xlsx]

(ooxml, embedded labels, table is Sheet1)

where NOT EXISTS(J_CUST_ID,J_REP_ID);

[luismadriz]

Hi, I would recommend not to exclude anything and use a flag that differentiates Sales from Assignments, that way you'll have all information there such as Sales where not Assigned or Assigned and no sales, etc

Cheers,

Luis