11 Replies Latest reply: Nov 8, 2016 5:48 PM by Sunny Talwar RSS

    Join Issue

    Venkata Nerella

      Hi,

       

      Fairly new to Qlik,trying to figure out whats wrong with this LEFT Join.

      I should have seen 8 rows from Table A but instead get a result with number of rows that exist in Table B(80K rows).

       

        Table_A:

        REPORT_DT,

      JOB_OPENING_ID

      FROM [lib://QVDs/Table_A.qvd](qvd)

      WHERE JOB_OPENING_ID = '201864';

      Left join (Table_A)

      FROM [lib://QVDs/Table_b.qvd](qvd);

       

      Also,

      I would like Both Table_A and Table_B to join based on Report_dt and JOb_Opening_id ;

      Will it automatically join based on common columns or should I create a concatenated key ?

        • Re: Join Issue
          Mark Ritter

          I think the problem is the semi-colon after the where clause.  Remove that and give it a try.

           

          If you have multiple fields that match then you will get a synthetic key.  So you should concatenate them into a key.

            • Re: Join Issue
              Venkata Nerella

              That didn't help.When I removed semi-colon like below, It throw syntax error.

               

              WHERE JOB_OPENING_ID = '201864'

               

               

              On Synthetic Keys,I wouldn't be loading the common columns again.I would select only the columns I need.

              So my question is,Can JOIN Command automatically pick two common columns from each table before it joins with the other table ?

            • Re: Join Issue
              Sunny Talwar

              What are you left joining here?

               

              Capture.PNG

               

              You missed the LOAD statement. Try this:

               

              Table_A:

              LOAD REPORT_DT,

                  JOB_OPENING_ID

              FROM [lib://QVDs/Table_A.qvd](qvd)

              WHERE JOB_OPENING_ID = '201864';

               

              Left join (Table_A)

              LOAD *

              FROM [lib://QVDs/Table_b.qvd](qvd);

                • Re: Join Issue
                  Mark Ritter

                  Good point.  Missed that.

                  • Re: Join Issue
                    Venkata Nerella

                    Sorry,I should have been more specific  with my code rather than stripping it.

                    I have the Load Statements already.

                      • Re: Join Issue
                        Sunny Talwar

                        Not sure I understand what you mean

                          • Re: Join Issue
                            Venkata Nerella

                            Sorry for the back and forth.

                            What I meant is that I have code similar to what you are suggesting

                            Table_A:

                            LOAD REPORT_DT,

                                JOB_OPENING_ID

                            FROM [lib://QVDs/Table_A.qvd](qvd)

                            WHERE JOB_OPENING_ID = '201864';

                            Left join (Table_A)

                            LOAD *

                            FROM [lib://QVDs/Table_b.qvd](qvd);

                              • Re: Join Issue
                                Sunny Talwar

                                I think Left Join will bring all the rows from Table_b where it finds the match in Table_A on the fields you are joining on? Have you checked the values are not matching for the joined fields? May be you have one addition field you need to join on?

                                 

                                Just as a side note, the field names need to match word-to-word and case-to-case for QlikView to know that you are joining the two tables on the intended fields

                                  • Re: Join Issue
                                    Venkata Nerella

                                    The Results didn't come back right even after I chose INNER JOIN

                                     

                                    Table A has REPORT_DT,   JOB_OPENING_ID

                                     

                                    and Table B has REPORT_DT,    JOB_OPENING_ID

                                     

                                    I'm assuming JOIN would automatically join based on these two Columns from two tables.

                                     

                                     

                                    Additionally I have created a single Join Key Column on both tables but it still doesn't join the values properly.

                                     

                                    Yes.I'm sure that The column names match exactly and Values are right

                            • Re: Join Issue
                              Venkata Nerella

                              Sunny,

                               

                              Finally figured out what the issue is.

                              During Join, I assumed that if I include all columns from all tables then data will get inserted as multiple columns. So I chose only the columns I needed in the output.

                              Table_A:

                              LOAD REPORT_DT,

                                  JOB_OPENING_ID

                              FROM [lib://QVDs/Table_A.qvd](qvd)

                              WHERE JOB_OPENING_ID = '201864';

                              Left join (Table_A)

                              LOAD

                              TARGETS

                               

                              FROM [lib://QVDs/Table_b.qvd](qvd);

                               

                              Which means I didn't include Join Condition columns in the statement.

                               

                              After I changed the Query to below,I get correct results.

                              Table_A:

                              LOAD REPORT_DT,

                                  JOB_OPENING_ID

                              FROM [lib://QVDs/Table_A.qvd](qvd)

                              WHERE JOB_OPENING_ID = '201864';

                              Left join (Table_A)

                              LOAD

                              REPORT_DT,

                                  JOB_OPENING_ID,

                              TARGETS

                               

                              FROM [lib://QVDs/Table_b.qvd](qvd);

                                • Re: Join Issue
                                  Sunny Talwar

                                  Have you checked in your data model that it is now only storing 8 rows? I am surprised it made a difference. I mean a DISTINCT left join would have been explainable, but this is somewhat confusing. Is it a bug? I am not sure...