9 Replies Latest reply: Sep 17, 2010 12:19 PM by Cesar Estrada RSS

    Select and Left Join

      Hi people:

      I confused and need work in Qlik one sentece from MSSQL, with the same result.

      The sentece is in MSSQL

      select * from tableheader left join tableitems
      on tableheader.tableheader_module = tableitems.tableitems_module
      and tableheader.tableheader_code = tableitems.tableitems_code
      and tableheader.tableheader_number = tableitems.tableitems_number

      I try this but the only same field with the same name is user in both tables,
      but is not primary key and when in the script send this sentece, and see
      viewer of tables (ctrl+T) the unique link is between the field "USER"


      CONNECT TO xxxxxx
      SELECT * FROM tableheader ;
      SELECT * FROM tableitems


      How can use JOIN in Qlik

      Thanks for all, regards

        • Select and Left Join
          John Witherspoon

          Something like this, I suspect. But if this is all you're doing, I see no reason to join in QlikView instead of in SQL. I'm also not sure where the USER field comes into the picture. But if you have a USER field on both tables named exactly the same, and you don't want to join on it, you'll need to rename one of them.

          MyTable:
          LOAD
          tableheader.tableheader_module as module
          ,tableheader.tableheader_code as code
          ,tableheader.tableheader_number as number
          ...
          ;
          SQL SELECT * FROM tableheader
          ;
          LEFT JOIN (MyTable)
          LOAD
          tableitems.tableitems_module as module
          ,tableitems.tableitems_code as code
          ,tableitems.tableitems_number as number
          ...
          ;
          SQL SELECT * FROM tabletimes
          ;

            • Select and Left Join

              Hi John

              Thanks for u response, i test but i recieved this error

              Syntax error, missing/misplaced FROM:
              MyTable:
              LOAD

              Any idea?

              Thanks

                • Select and Left Join

                  Sorry the complete error is

                  Syntax error, missing/misplaced FROM:
                  MyTable:
                  LOAD
                  tableheader.tableheader_module as module
                  ,tableheader.tableheader_code as code
                  ,tableheader.tableheader_number as number
                  ...

                  MyTable:
                  LOAD
                  tableitems.tableitems_module as module
                  ,tableitems.tableitems_code as code
                  ,tableitems.tableitems_number as number
                  ...

                  Regards

                  • Select and Left Join
                    John Witherspoon

                    Can you post your actual script, or at least this section of it? And just in case you were confused by it, my code DOES have syntax errors, in that the '...' should be replaced with the rest of the fields you are selecting, and not copied directly into your code. It also confuses me that you had the table name and then a dot before the field name, so perhaps that's not correct, but I was just copying what you had in that case.

                    Edit: OK, from the complete error, unless you mean for the '...' to indicate that there are lines of error that you cut out, it looks like you didn't add the other fields from your table. You need to load the fields that you want, and not have '...' directly in the code.

                      • Select and Left Join

                        The script

                        CONNECT TO [Provider=MSDASQL.1;Persist Security Info=False;
                        Extended Properties="Description=SQL3;
                        DRIVER=SQL Server;SERVER=SQL3;UID=;
                        APP=QlikView;WSID=SQL5;DATABASE=Emp;
                        Trusted_Connection=Yes";Initial Catalog=Emp];

                        MyTable:
                        LOAD
                        tableheader.tableheader_module as module
                        ,tableheader.tableheader_code as code
                        ,tableheader.tableheader_number as number
                        from tableheader
                        ;
                        SQL SELECT * FROM tableheader
                        ;
                        LEFT JOIN (MyTable)
                        LOAD
                        tableitems.tableitems_module as module
                        ,tableitems.tableitems_code as code
                        ,tableitems.tableitems_number as number
                        from tableitems
                        ;
                        SQL SELECT * FROM tableitems
                        ;
                        ----------------------
                        The error

                        Cannot open file 'C:\Program Files\QlikView\Ejemplos\Documents\tableheader'
                        MyTable:
                        LOAD
                        tableheader.tableheader_module as module
                        ,tableheader.tableheader_code as code
                        ,tableheader.tableheader_number as number
                        from tableheader

                        -----------------------------------------------------------------------------------------------------------

                        Many Thanks!

                         

                          • Select and Left Join
                            John Witherspoon

                            CONNECT TO [Provider=MSDASQL.1;Persist Security Info=False;
                            Extended Properties="Description=SQL3;
                            DRIVER=SQL Server;SERVER=SQL3;UID=;
                            APP=QlikView;WSID=SQL5;DATABASE=Emp;
                            Trusted_Connection=Yes";Initial Catalog=Emp];

                            MyTable:
                            LOAD
                            tableheader.tableheader_module as module
                            ,tableheader.tableheader_code as code
                            ,tableheader.tableheader_number as number
                            REMOVE THIS LINE AND LIST THE OTHER FIELDS YOU NEED HERE
                            //from tableheader REMOVE THIS LINE TOO
                            ;
                            SQL SELECT * FROM tableheader
                            ;
                            LEFT JOIN (MyTable)
                            LOAD
                            tableitems.tableitems_module as module
                            ,tableitems.tableitems_code as code
                            ,tableitems.tableitems_number as number
                            REMOVE THIS LINE AND LIST THE OTHER FIELDS YOU NEED HERE
                            //from tableitems REMOVE THIS LINE TOO
                            ;
                            SQL SELECT * FROM tableitems
                            ;

                              • Select and Left Join

                                This is the result:

                                First Error

                                Field not found - <tableheader.tableheader_module>
                                SQL SELECT * FROM tableheader

                                Second Error

                                Table not found
                                LEFT JOIN (MyTable)
                                LOAD
                                tableitems.tableitems_module as module
                                ,tableitems.tableitems_code as code
                                ,tableitems.tableitems_number as number


                                In the "Scrip Execution Progress"

                                MyTable << tableitems 711,256 lines fetched

                                Thanks, regards

                                  • Select and Left Join
                                    John Witherspoon

                                    OK, so replace the wrong field name with the RIGHT field name. Then the first table can load, which should resolve the second error as well.

                                      • Select and Left Join

                                        Hi John:

                                        1- Thanks for all

                                        2- At last, this work for my

                                        SQL SELECT
                                        tableheader.tableheader_module as module
                                        ,tableheader.tableheader_code as code
                                        ,tableheader.tableheader_number as number
                                        ,* from tableheader
                                        ;
                                        SQL SELECT
                                        tableitems.tableitems_module as module
                                        ,tableitems.tableitems_code as code
                                        ,tableitems.tableitems_number as number
                                        , * from tableitems
                                        ;

                                        Regards
                                        César