3 Replies Latest reply: Apr 23, 2009 4:25 PM by Oleg Troyansky RSS

    6 Table Join Question

      Morning,

      As my first post, and being relatively new to QlikView, im trying to do the following. I have 6 tables which all have a field in common INSTANCE_ID but due to the huge amounts of data within the tables, it isnt practical to retrieve the whole record set for each table. I started that approach on a test database and recovere 30k records on the table i need with the below

      Table1:
      SQL
      SELECT *
      FROM
      "DB"."table1"
      WHERE
      "PRODUCT_LINE"='Equities' or "PRODUCT_LINE"='FixedIncome';;
      store
      Table1: into c:\Table1:.qvd;
      drop table Table1:;

      yet table 2 has in excess of 7 million records ( i dont know how many as 3 hours into the retrieval I had to cancel), and this will be the same for tables 2-6 no doubt, and i will need pretty much daily downloads.

      I need ideally say Select * from table2 where Exists INSTANCE_ID,

      Id like to achieve this without actucally pulling in every record from every table, is this possible with a join? is this possible with a select where so that I can keep seperate QVD's?

      Any help is appreciated.
      Thanks
      Chris

        • 6 Table Join Question
          Peter Rieper

          Hi,

          Amount of records does not look unusually high ...

          with that long retrieval time, would guess that either the connection to the SQL-server is not that good, or that the fields you are investigating in the WHERE-clause are not indexed.

          Option 1 is a more technical one, for option 2 we frequently load all data and then do the necessary filtering in QlikView, once the data has been loaded into memory.

          HTH

            • 6 Table Join Question

              Hi, thanks for the relpy,

              I really couldnt comment on the connection and database speeds, all I know is we're using an Oracle back end. Once I have a solution in place then ill check with the Admin of the server to see what load the full download has on the test server, as too much and we wont be given access to the live environment.

              could you possible spare some time to elaborate a bit on a method for option 1? I can manage option 2 with my current solution, and will look to test over night.

              Thanks,

              Chris

                • 6 Table Join Question
                  Oleg Troyansky

                  Chris,

                  you obviously don't want to pull millions of records that you don't need... In order to avoid dragging all the records into QlikView and then filtering them in QlikView, you'll have to do it at the database level, using SQL functionality. Something along the following lines:

                   

                  SQL SELECT ... FROM Table1

                  WHERE Key1 in (Select Key1 FROM TABLE2 WHERE F1 = 1 and F2=2)

                   

                  Obviously, if your database tables are large, you need to be aware of your database structure - do you have an index on those fields that your are linking on or filtering on, etc... If you are not the "owner" of your database, I'd suggest that you consult with your DBA.

                  good luck!

                  Oleg