5 Replies Latest reply: Feb 19, 2017 9:10 PM by denise chia RSS

    Loading data and joining issue

    denise chia

      Hi all,

       

      Have looked through the discussion for a solution but to no avail.

       

      I have 2 tables

      A - Identity Number, Visit Date, Amount_1

      B - ID No., Admitted Date, Amount_2

      Table A

      Identity NumberVisit DateAmount
      11/2/1992342
      21/3/19927
      21/3/199234

      Table B

      Identity NumberAdmitted DateAmount
      21/3/2001645
      21/3/200145
      51/4/200290

       

      I am trying to load 2 different tables with common fields Identity Number as they are named differently i managed to do this by renaming the field from table A to ID No. also.

       

      However, I faced an issue on the data when creating charts in Qlik Sense.

      I am trying to create a stacked bar chart based on

      x axis - ID No. and Visit/Admitted Date (should be displayed as 1 field)

      y axis - Total # of line items from table A and B

      color - Type (line items from table A as type 1, line items from table B as type 2).

       

      After creating the chart I realized that as the ID No. was combined together, the Visit Date and Admitted Date are reflected on the same line for same ID No.

      This is an issue when I have the same ID No. with 2 different dates from table A and B.

      Identity NumberVisit DateAdmitted DateAmount
      11/2/1992342
      21/3/19921/3/20017
      21/3/19921/3/200134
      21/3/19921/3/2001645
      21/3/19921/3/20015
      51/4/200290

      As the chart takes the first date available from Visit/ Admitted Date to plot the chart and as a result the number is a combination of line items from both dates the values are reflected inaccurately.

       

      Current:

      Bar 1 - ID No.1, 1 instance on 1/2/1992 from Type 1

      Bar 2 - ID No.2, 2 instances on 1/3/1992 from Type 1

      Bar 3 - ID No.2, 2 instances on 1/3/1992 from Type 2

      Bar 4 - ID No.5, 1 instance on 1/4/2002 from Type 2

      Correct:

      Bar 1 - ID No.1, 1 instance on 1/2/1992 from Type 1

      Bar 2 - ID No.2, 2 instances on 1/3/1992 from Type 1

      Bar 3 - ID No.2, 2 instances on 1/3/2001 from Type 2

      Bar 4 - ID No.5, 1 instance on 1/4/2002 from Type 2

       

      I am thinking that there is something wrong with the way the data was loaded but could not come to a conclusion on the right way to do it. Seek your kind responses. Thank you!

        • Re: Loading data and joining issue
          Tom McCormick

          See if you get better results by adding concatenate before the next similar data is loaded by the script (e.g  Concatenate LOAD instead of just load). if the data is close to matching.  Just a thought,


          T

            • Re: Loading data and joining issue
              denise chia

              Hi T,

               

              The data fields are very different from each other the tables above are just a snippet of it.

              I have tried concatenating before but the 2 tables does not seem to link when i do so.

               

              I created a new field indicator in Qlik for both table A and table B so table A would have rows with Indicator - Type 1 and table B Indicator - Type 2 and the bar graph seems to work.

               

              However when displaying pie charts the data does not seem to link well thinking that the way i loaded my data does not seem to be right. I have added my app for better understanding.

               

              Thank you so much!

               

              D

                • Re: Loading data and joining issue
                  Tom McCormick

                  I'll take a look... working on a project atm.  I might ask for your help next

                  • Re: Loading data and joining issue
                    Petter Skjolden

                    It is important to understand the relationship between the two tables so we can suggest the right way forward.

                     

                    First of all by introducing a new common field you create a synthetic key in you data model - which is most often not very good.

                     

                    Can you confirm whether:

                     

                    The Park had a number of Exhibitions right? And the Identity Number is a person that is admitted to a park and goes to 0 or more exhibitions? The Admitted Date is not necessarily the same date as Visit Date is it? It could be or it could be some following dates?

                     

                    If the only thing that should link the two tables is the Identity Number then you should name the Identicator differently like you do with the other fields except the Identity Number.

                     

                    We can get back to how to graph this as long as you confirm how the fields and tables should relate...

                      • Re: Loading data and joining issue
                        denise chia

                        Hi,

                         

                        Noted on the disadvantages of synthetic keys.

                         

                        Park and Exhibitions visits are not related. However I am trying to link the 2 tables with Identity Number so that I am able to find out how many of the same people visited Exhibition and Park on the same date/ with the same status.

                         

                        I have managed to come out with something using the qualify method. May I seek your kind advice if this is an appropriate way to proceed?

                         

                        Thank you in advance!