4 Replies Latest reply: Feb 23, 2017 8:25 AM by Alfie Ang RSS

    Need help on data association

    Alfie Ang

      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

        • Re: Need help on data association
          Andy Weir

          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

            • Re: Need help on data association
              Alfie Ang

              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.

            • Re: Need help on data association
              Santosh Vinnakota

              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.

              • Re: Need help on data association
                Jayaseelan K

                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,