Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
azubrewar
New Contributor

Qlik Datamodel question

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.

Datamodel.png

Tags (2)
9 Replies
vvira1316
Valued Contributor II

Re: Qlik Datamodel question

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
Valued Contributor

Re: Qlik Datamodel question

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
Valued Contributor

Re: Qlik Datamodel question

And by combine I mean concatenate one after the other

Cheers,

Luis

Partner
Partner

Re: Qlik Datamodel question

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

Re: Qlik Datamodel question

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

Re: Qlik Datamodel question

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.

Partner
Partner

Re: Qlik Datamodel question

Hi,

Using Applymap function must solve your problem.

Regards,

Snehal Nabar

azubrewar
New Contributor

Re: Qlik Datamodel question

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);

Sales table.png

luismadriz
Valued Contributor

Re: Qlik Datamodel question

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