9 Replies Latest reply: Dec 2, 2017 6:30 PM by Luis Madriz RSS

    Qlik Datamodel question

    ashok zubrewar

      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

        • Re: Qlik Datamodel question
          VIJAY VIRA

          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.

          • Re: Qlik Datamodel question
            Luis Madriz

            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

            • Re: Qlik Datamodel question
              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

              • Re: Qlik Datamodel question
                Bill Markham

                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
                  snehal nabar

                  Hi,

                   

                  Using Applymap function must solve your problem.

                   

                  Regards,

                  Snehal Nabar

                  • Re: Qlik Datamodel question
                    ashok zubrewar

                    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