topic Re: Creating a Calculated Table in Load Script from Multiple Tables in Load in QlikView

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

zach_paz — Wed, 02 Sep 2015 18:16:05 GMT

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

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

MarcoWedel — Wed, 02 Sep 2015 21:59:23 GMT

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

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

zach_paz — Fri, 04 Sep 2015 19:51:03 GMT

I think this works! Thank you!

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

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

MarcoWedel — Fri, 04 Sep 2015 21:23:47 GMT

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