3 Replies Latest reply: Sep 4, 2015 5:23 PM by Marco Wedel RSS

    Creating a Calculated Table in Load Script from Multiple Tables in Load

    Zach Paz

      Hi All,

       

      I have a very large table to make in one of my QVWs that I was hoping to move into the Load Script since the chart runs our of object memory on me (40 million records in QVW).

       

      I first load in a few tables:

       

      Inquiries:
      LOAD lead_id,
            qualified,

            conversion,
            Date,
            census_geoid,
            code,
            degree,

            Type
      FROM
      [C:\Users\xxx\all.QVD]
      (qvd);

       

      Cleanup_census_geoid:

      LOAD census_geoid,

            geoid

      FROM

      [C:\Users\xxx\geoid.QVD]

      (qvd);


      Cleanup_degree:

      LOAD degree,

            AWL

      FROM

      [C:\Users\xxx\awl.QVD]

      (qvd);


      I then would like to create the following table:

       

      Dimensions:

      • geoid
      • code
      • AWL

      Expressions:

      • sum(if(Date>'7/31/2014',qualified)) as TrailingTwelveInquiries
      • sum(if(Date>'7/31/2013' AND Date<='7/31/2014',qualified))  as PriorTwelveInquiries
      • sum(if(Type='Online' AND Date>'7/31/2014',qualified))  as TrailingTwelveInquiriesOnline
      • sum(if(Date>'7/31/2013' AND Date<='7/31/2014' AND Type='Online',qualified))  as PriorTwelveInquiriesOnline
      • sum(if(conversion='1' AND Date>'7/31/2014',qualified))  as PriorTwelveConversions

       

      My goal is to have this table pre-calculated, since it runs our of object memory. I would also like to save it as a QVD in the LOAD Script to use in other QVWs.

       

      Please let me know if you have any questions.

       

      Thanks in advance for your help!

       

      Zach

        • Re: Creating a Calculated Table in Load Script from Multiple Tables in Load
          Marco Wedel

          join geoid.QVD and awl.QVD to Inquiries while loading instead of generating seperate tables.

          Then create the aggregated table like

           

          LOAD
          geoid,
          code,
          AWL,
          sum(if(Date>'7/31/2014',qualified)) as TrailingTwelveInquiries,
          sum(if(Date>'7/31/2013' AND Date<='7/31/2014',qualified))  as PriorTwelveInquiries,
          sum(if(Type='Online' AND Date>'7/31/2014',qualified))  as TrailingTwelveInquiriesOnline,
          sum(if(Date>'7/31/2013' AND Date<='7/31/2014' AND Type='Online',qualified))  as PriorTwelveInquiriesOnline,
          sum(if(conversion='1' AND Date>'7/31/2014',qualified))  as PriorTwelveConversions
          Resident Inquiries
          Group By geoid, code, AWL;
          


          hope this helps

           

          regards

           

          Marco