0 Replies Latest reply: Dec 15, 2010 3:45 PM by Trey Bayne RSS

    QlikView Optimization

       

      This scenario comes up often for me and I hope somebody out there has a better way. The examples below are just that, examples and don't reflect real data. I don't normally use *'s in my load statements;

      I'll have to load a total of 4 tables from 4 different data sources. Two tables will be joined to each other and then the other two will be joined to each other, leaving me with two logical tables.

      tab1:

      load * from abc;

      left join

      load * from def;

      tab2:

      load * from uvw;

      left join

      load * from xyz;

      After I have used the joines to filter out some of the data I don't want, I need to do a calculation on the on the combined logical tables that resulted from the join. The only way I've figured out how to do this is by loading tab1 and tab2 using a resident load, concatenating and calculating using a preceeding load. See below.

      tab1:

      load * from abc;

      left join

      load * from def;

      tab2:

      load * from uvw;

      left join

      load * from xyz;

      NOCONCATENATE

      combinedtables:

      LOAD COUNT(WIDGET) GROUP BY column2;

      LOAD * resident tab1;

      concatenate

      LOAD * resident tab2;

      DROP TABLE tab1,tab2;

      The problem with this is the waste of reloading existing tables using the resident load. Not only is resident load slow it eats up extra ram that I often don't have a lot of extra of during the load process by loading the same data twice.

      If you try the following you end up with uvw being concatenated with def and then xyz being left joined to abc,def and uvw.

      tab1:

      load * from abc;

      left join

      load * from def;

      concatenate

      load * from uvw;

      left join

      load * from xyz;

      Any Suggestions?