Reduce by Field Value in Qlik Sense

[PhilipMertin]

In Qlikview QMC, there is an option to Reduce by Field Value in the reload task, which then reduces the data model to that field value and distributes a separate document for each value in the selected field. All data not included in the selection will be removed.

This allows for a base app to be used, to distribute smaller front-end apps.

Is there a similar feature available in Qlik Sense?

If you sit with a very large and complex data model, there's a risk in trying to adjust the load script to only have the required data. A lot of work will be required and logic might be distorted.

[Dalton_Ruer]

Qlik Sense has you covered on this in it's ability to do On Demand Application Generation. The concept is simple: End User selects the cohort they want, (equivalent to them picking up a phone, calling you, you filtering, you reducing, you saving, you giving them QVW) then they press a button which opens a "template" application on the fly, that application loads the data needed for their selected cohort and they work with that set. 

The template application can either load from QVD files so they have the same data consistently like they would have gotten in a reduced data set QVW, OR, you can have it load the data on the fly for latest/greatest. The "on the fly" part can be used to solve the BIG DATA issue where you have billions and billions of rows of data and can't fit it all in a single application, or on the fly can solve the "my users want near real time" data so if something changed in the last few minutes they see it problem. 

This blog post I wrote will describe the concept and provide coding example of how to build it and point to additional help. The post advocates for ODAG's cousin, Dynamic Views, as the use case, but you can ignore that as in your case you likely want the ODAG approach itself. 

https://dataonthe.rocks/dynamic-views-in-qlik-sense-saas/

[PhilipMertin]

It seems with ODAG that one has to specify where clauses in the load script. This is not much different than trying to limit the app for a specific division or whichever dimension value. So this would not be a feasible solution for me, as the data model and script is so complex, that it would leave the risk of getting the where clauses wrong and/or not applying them everywhere. Not the same functionality as reducing with distribution at all.

[kdmarkee]

Great post. I need the same functionality as well.  We build one huge QVW which holds all of our clients data and I use a Publisher task in the QV QMC with the applicable client code in Reduce by Field Value/Simple Reduce (a task per client) so that each client only has their data loaded into their client specific QVW.  We use the one huge QVW internally only so our company can monitor analytics on all our clients as a whole.  If I think of any efficient way to implement this in QS, I'll report back.

[PhilipMertin · Accepted solution]

In the meantime, I have considered using Qlik Sense Section Access and in essence it should take care of it, since the app should reduce in memory to what the user is allowed access to.. 

[kdmarkee]

We also use Section Access because each user of a client (group of people) can only see their own transactional records.  I don't want our clients app experience to be sluggish by loading all data and only using Section Access, which is why I like the Simple Reduce...less data the app has to work with making UI performance better for the client.

[Johannes_Twisk]

I have tested to do this in just the load-script at the "end" of the applications load-script. It works perfectly, it even works in combination with Section Access. I will just explain the concept.

Prerequisites: Needs developer that is able to do "Advanced"? LOAD-script programming. 

Big advantages: JUST "simple" LOAD-script. NO Section Access is needed for reduction. NO use of any API.

PS. I only tested it with 1 Field. But I think it should work, even with more than one field easily if the fields are in the same table, otherwise it could become to complex.

The implementation/concept (working!):

Write a LOAD script that you put at the END (but before possible Section Access script). The script should contain the following steps:

1. Find the "Main" table that contains the field you want to reduce on.
2. Do a Resident-load from this table with a WHERE-clause based on your Reduce-field-filter. Now the "Main" table is reduced.
3. Find all tables that have a DIRECT relation to the "Main" table, BUT EXCLUDE the "Main" table itself (that already has been reduced). Do a Resident-load with a LEFT KEEP ("Main" table) on all of these tables. Now these tables have been reduced.
4. Find all tables that have a DIRECT relation to the above tables (see 3), BUT always EXCLUDE all tables that already have been reduced (e.g. keep av variable that contains a list of your already excluded tables).
5. Repeat STEP 4 until there are no more tables left to reduce...
6. You now have an application that contains REDUCED data.

Some implementation thoughts:

1. Write a LOAD script that can be placed/injected in every app (Execute/Call it at the end of the load script (BUT before any Section Access)) in the application. 
2. Have a configuration-file in some folder that contains your Reduce-information. You probably need the app-id, fieldname and the value(s) you want to keep in the data-model.
   
   PS You should also be able to able to Reduce based on combination of STREAM-name AND application-id. You can find the stream name (in the load-script) by running this SQL (SQL SELECT "ID", "Name" FROM "QSR"."public"."Streams";) against the QlikRepository Database.
3. To speed up things, run just a Binary LOAD from the "mother"-app (if possible) and after that execute your script.
4. There are advantages/disadvantages, JUST LIKE with Section Access. It depends on your situation...
5. To find all relations between all tables use something like the following script (OBS! Remove all records where TABLE1 = TABLE2) :
   ALL_TABLES_AND_FIELD_RELATIONS:
   Load
   AppTable AS TABLE1
   ,AppField AS THEFIELD
   Resident TABLESANDFIELDSINAPP
   ;
   INNER JOIN (ALL_TABLES_AND_FIELD_RELATIONS)
   Load
   AppTable AS TABLE1
   ,AppField AS THEFIELD
   Resident TABLESANDFIELDSINAPP
   ;

Hope this helps!

John