Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
zach_paz
New Contributor II

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

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

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

3 Replies

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

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
New Contributor II

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

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

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

Community Browser