Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

Need help on data association

Hi All,

I am pretty new in this and appreciate your kind advice on data association.

My data model is shown below:

Qlik Sense Data Model.PNG

Left table is my sales data and right table is my claims data.

There are few fields that are common in both tables.

1. Created On = DOCUMENT DATE

2. S-t-P = IMPORTER NUMBER

3. Material = PARTS NUMBER

I would like to visualise following:

1. Claims vs Sales (%) per month

2. Claims vs Sales (%) by Customer

3. Claims vs Sales (%) by Part Description

Before I could create the visualisations, can somebody guide me on associating both data tables?

Thanks!!!

Alfie

1 Solution

Accepted Solutions
svinnakota
Contributor

Re: Need help on data association

Hi Alfie,

You can create the Key Column like this.

[Created On]&'|'&[S-t-P]&'|'&[Material] as  [KeyColumn]

[DOCUMENT DATE]&'|'&[IMPORTER NUMBER]&'|'&[PARTS NUMBER] as [KeyColumn]

The above keys are for the respective tables and the association will be done based on that. Join  the main Columns and make the key column so that the data wont be cross join.

Tell me whether it works or not.

Regards,

Santosh.

4 Replies
ogster1974
Honored Contributor II

Re: Need help on data association

Sounds like you should model your data like this.

Create the following dimension tables

Sales - > Unique sales fields

Claims - > Unique claim fields

Products -> Product name, Product Type

Customers -> Customer Name, Customer Type

You will need to create unique keys perhaps concatenating 2 or 3 fields to make the unique value if you do not have a unique sales or claim number.

Create a Fact table and load the common data into it.

ClaimsKey

SalesKey

ProductsKey -> Material = PARTS NUMBER

CustomerKey -> S-t-P = IMPORTER NUMBER

Fact Type = 'SALES VALUE (Euro), CLAIMS NET VALUE, CLAIMS NET VALUE (Euro) , Quanties Etc..'

Date -> Created On = DOCUMENT DATE

Value

Do this and you should have a model you can use to answer the questions you have asked and more.

Hope this helps

Regards

Andy

Not applicable

Re: Need help on data association

Hi Andy,

Appreciate your help.

I have also searched online for some guides and I created a LinkTable to connect both fact tables as shown below.

DataModel.PNG

I am able to plot something like this:

Capture1.PNG

But when i filter by conclusion, the line graph shows 0%:

Capture2.PNG

Can you please assist further?

Thank you.

svinnakota
Contributor

Re: Need help on data association

Hi Alfie,

You can create the Key Column like this.

[Created On]&'|'&[S-t-P]&'|'&[Material] as  [KeyColumn]

[DOCUMENT DATE]&'|'&[IMPORTER NUMBER]&'|'&[PARTS NUMBER] as [KeyColumn]

The above keys are for the respective tables and the association will be done based on that. Join  the main Columns and make the key column so that the data wont be cross join.

Tell me whether it works or not.

Regards,

Santosh.

jayaseelan
Contributor III

Re: Need help on data association

Hi Alfie,

You Can do the association using composite key.

For Sale2016 Table:

num([Created On])&'-'&[S-t-P]&'-'&[Material] as  [Key]


convert [Created on] into num format else it will become trouble.


For Raw Claims Data Table:


num([DOCUMENT DATE])&'-'&[IMPORTER NUMBER]&'-'&[PARTS NUMBER] as [Key]

The two keys automatically get associated.


Thanks,