topic Re: Dynamic Load Statements in QlikView

Dynamic Load Statements

Tue, 20 Nov 2012 16:39:22 GMT

Is there a way to make the Load Statement dynamic so that the dataset pulled for the QVW is only the records the user wants?

We have two normalized tables that, once denormalized, grows to a set of over 6 million rows. The user wants to report on these data; but wants to choose which records based on criteria in the original tables.

An example:

Table A (50 total rows)

DefID 1,2,3,4

DefName Pots, Pans, StoreNumber, Status

DefType Char, Char, Num, Bool

DefSequence 17,1,5,30

Table B (6 mil + rows)

DefID 1,1,2,4,3

CustID 1001,1002,1003,1004,1004

DefValue Copper,Brass,FlatIron,0,27

DeNorm Table

A.DefID,A.DefName,A.DefType,A.DefSequence,B.DefID,B.CustID,B.DefValue

1,Pots,Char,17,1,1001,Copper

1,Pots,Char,17,1,1002,Brass

2,Pans,Char,1,1003,FlatIron

4,Status,Bool,30,1004,0

3,StoreNumber,Num,5,1004,27

A common report would be to give all the Pots for Customer 1004; or All Customers with FlatIron Pans.

What we need is to ask the Load statement to only load those records with the A.DefName(s) = [var] , or B.CustID(s) = [var] or, A.DefNames for B.CustID(s).

Is this possible?

I am relatively new to QV and come from a Crystal Reports background where I would have a paramterized Select statements that ask for parameters first;

then use those selections in the record select; but I am not sure how to accomplish this in QV.

I have looked at posts by

John Witherspoon or Greg Hood; but cannot find anything addressing this specific type of action.

Re: Dynamic Load Statements

Anonymous — Tue, 20 Nov 2012 18:00:51 GMT

You can have LOAD or SELECT statements with the conditions based on variables, e.g.:
SELECT A, B, C,..
FROM DataTable
WHERE A=$(Var1);

If user can define a variable and reload - you have what you need.

Regards,
Michael

Re: Dynamic Load Statements

rwunderlich — Tue, 20 Nov 2012 19:00:18 GMT

Typically, the QV way is to load all 6 million rows and let the user filter by selections in listboxes. That is faster and more flexible.

-Rob

Re: Dynamic Load Statements

Anonymous — Tue, 20 Nov 2012 19:20:04 GMT

Also true, this is what QlikView is about.

Besides, user can reduce the file if needed without any reload and without variables. Just make selections, and click File -> Reduce Data -> Keep possible values.

Regards,
Michael

Re: Dynamic Load Statements

Tue, 20 Nov 2012 20:06:41 GMT

Please help me to understand how loading all 6mil records is faster than only loading say 500,000? I have to think that the less data pulled at run-time, the faster the report loads. Our users interact with reports that are hosted on our server but they do not change them as a developer might, re-load, file, etc are all locked out and they are only end users in the strictest sense.

Re: Dynamic Load Statements

Tue, 20 Nov 2012 20:10:06 GMT

Would this keep the initial data set from loading until a variable is filled out? Or would you put in a default value that returns a null until a var is put in? Our users can't reload at will; is there a statement that can be run as a pre-load?

Re: Dynamic Load Statements

MayilVahanan — Tue, 20 Nov 2012 20:18:39 GMT

PFA

Are you looking like this?

Re: Dynamic Load Statements

rwunderlich — Tue, 20 Nov 2012 20:26:20 GMT

It's faster in the sense that you don't have to reload each time you need a different set of records. You load all 6 million once. Let's say that takes 15 minutes. It's batch, it runs overnight and no user is waiting.

Now each user uses the same 6mil row dashboard. User A wants products A,B,C. Click. In a few seconds they have a completed report. User B wants products X,Y,Z. Click. In a few seconds they have a report.

If I understand your original question, you are asking about rerunning the script each time the user wants a different set of data. That woud take minutes and a user would be waiting for the result.

That's what I meant by faster. Am I understanding your requirement?

-Rob

Re: Dynamic Load Statements

Anonymous — Tue, 20 Nov 2012 20:28:00 GMT

"Users cannot reload by will" - are you saying that the application is on QV server? In this case Rob is 100% correct. Reloading application with 6 mln records once is faster than reloading many applications with 500K records, which can be done using Publisher. And, one application accessed by many users takes fewer resourses than multiple applications with one user each.

Regards,
Michael

Re: Dynamic Load Statements

Tue, 20 Nov 2012 21:13:01 GMT

Thank you guys for the quick responses, as you can tell I am new to the whole QlikView thing and while I've done tons of reading in both the QV Dev I and II books, I have a long way to go yet.

Rob, you are right, in that we are using QV server and then using Publisher so that it is one pull to many uses.

However, my manager would like to get away from pulling large sets of data for reports, since our DB is already overworked and tends to fail on large data requests.

Also, the users want a more "live" view so that they can have data that is not overnight; but within minutes of updating the database.

Mayil, thank you for the example; it is the closest to being a parameterized load; however, it still requires a re-load to trigger.

Re: Dynamic Load Statements

rwunderlich — Wed, 21 Nov 2012 16:58:18 GMT

An approach to the database resource issue is to implement incremental (delta) loads where you select only the new & updated rows and merge with a qvd file.

-Rob