Qlik Community

Ask a Question

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

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

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

1 Solution

Accepted Solutions

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

View solution in original post

3 Replies

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

View solution in original post

Contributor III
Contributor III

I think this works! Thank you!

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

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