1 Reply Latest reply: May 16, 2011 9:40 AM by Nigel West RSS

    sql sub query from a resident load

      hi All,

       

      is it possible to add an sql sub query when loading a table from a resident?

       

      i have 2 very big files that i need to join.

       

      i need to load the first one and reduce the number of records and then usethe reduced size table to do a join on the second file.

       

      left join wont work since i must use having statement in the first SQL load,so if i try to add a left join it happens before the number of records isreduced by the having statment.

       

      i would like to load table with sql and then load that same table asresident and add a sub query to another fie.

       

      is it possible?

       

      i hope i explained myself correctly.

       

      this is the load from the first file

       

      TBL1:

      load

           a, b, c

      sql select a, b, sum(c) as c from file1

      group by a,b

      having sum(c) > 0

       

      now i need to add data from file2 using fileds a and b from TBL1

      if I do it in the same sql statement (left join or inner join) the scriptget stuck due to the size of the files.

      And i dont want to load the entire file2 as a table because it huge.

      Any suggestions?

        • sql sub query from a resident load

          Hello

           

          Have you tried doing something like:

           

          TBL1:

          load

               a, b, c, a&b as key

          sql select a, b, sum(c) as c from file1

          group by a,b

          having sum(c) > 0;

           

          INNER JOIN

          load

              d, e, f, e&f as key

          sql select d,e,f from file2;

           

          Think that should work for you but not sure about order of load, think it will execute first load fully (with the having clause) and then join to the second load. the "key" field is the field that QlikView will use to join on.

           

          Good luck,