6 Replies Latest reply: Feb 22, 2011 2:01 PM by John Witherspoon RSS

    urgent: Joining two sql statements with more than 1 common filed in QV

    Anil Konduri

      Hi, How to join two sql statements in qv without synthetic key as because I have 3 fileds in common and I have to join them using all three columns and I cannot rename fields

      Ex: -

      Query 1:

      Select

      col1,

      col2,

      col2,

      ----

      ----

      from

      tab1,

      tab2,

      tab3,

      where condition..............

      Query 2:

      Select Col1, col2, col3, cq from tabx

       

      SO I have to join Query1 & Query2 using Col1, col2, col3. How?

       

      Thanks in Advance

        • urgent: Joining two sql statements with more than 1 common filed in QV
          Miguel Angel Baeyens de Arce

          Hello,

          You may use CONCATENATE keyword to just "union" your results:

          LOAD *; SQL SELECT ...; CONCATENATE LOAD *; SQL SELECT ...;
          Is this useful to you?

          Regards

            • urgent: Joining two sql statements with more than 1 common filed in QV
              Anil Konduri

              Hi Miguel, Thanks for your reply...

              No, I don't want to simple union the results.. i want to join using all three columns like

               

              Query1.col1 = Query2.col1

              OR/And

              Query1.col2 = Query2.col2

              OR/And

              Query1.col3 = Query2.col3

                • urgent: Joining two sql statements with more than 1 common filed in QV
                  John Witherspoon

                  You CAN remove the synthetic key, something like this:

                  KeyTable:
                  LOAD DISTINCT
                  col1 as col1x
                  ,col2 as col2x
                  ,col3 as col3x
                  ,autonumber(col1 & ';' & col2 & ';' & col3) as key
                  RESIDENT Table1
                  ;
                  OUTER JOIN (KeyTable)
                  LOAD DISTINCT
                  col1 as col1x
                  ,col2 as col2x
                  ,col3 as col3x
                  ,autonumber(col1 & ';' & col2 & ';' & col3) as key
                  RESIDENT Table2
                  ;
                  LEFT JOIN (Table1)
                  LOAD
                  col1x as col1
                  ,col2x as col2
                  ,col3x as col3
                  ,key
                  RESIDENT KeyTable
                  ;
                  LEFT JOIN (Table2)
                  LOAD
                  col1x as col1
                  ,col2x as col2
                  ,col3x as col3
                  ,key
                  RESIDENT KeyTable
                  ;
                  DROP FIELDS
                  col1
                  ,col2
                  ,col3
                  ;
                  RENAME FIELDS
                  col1x as col1
                  ,col2x as col2
                  ,col3x as col3
                  ;

                  Now, there may be a simpler sequence that would accomplish the same thing. However, although we've now eliminated our synthetic key, we haven't actually accomplished anything. This is basically exactly what a synthetic key DOES. It creates a separate key table with all of your key values, almost certainly gives each distinct combination a unique ID internally, and then puts this unique ID back on the original tables in place of the original fields. OK, I'm just guessing on the implemenation, but some brief testing I did showed very similar memory usage to a QlikView supplied synthetic key and an explicitly-supplied key like I created above.

                  The testing I did also showed very slightly better memory usage and performance in the synthetic key version.

                  So while you CAN eliminate the synthetic key by the method shown above, all it will do it waste processing time in the load. It doesn't really buy you anything. If you have an actual problem that you believe is CAUSED by the synthetic key, using the script above isn't likely to solve it, because it isn't likely a synthetic key problem. It's likely a more fundamental data problem that merely making an explicit key is unlikely to resolve.

                  In other words, unless you have an actual problem, don't worry about the synthetic key. If you do have a problem, you'll need to explain it, because merely removing the synthetic key is unlikely to fix it.