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

(qvd);

Cleanup_census_geoid:

LOAD census_geoid,

      geoid

FROM

(qvd);

Cleanup_degree:

LOAD degree,

      AWL

FROM

(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

[MarcoWedel · Accepted solution]

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

[zach_paz]

I think this works! Thank you!

Any chance I can save that table as a QVD in the script also?

[MarcoWedel]

Hi,

yes,

name the table and save it using "store" like

tabYourtable:
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; 

Store tabYourtable into C:\replace\with\your\path\tabYourtable.qvd (qvd);

hope this helps

regards

Marco