Can we 'Reduce Data' with 'Keep Possible Values' in Qlik Sense ?

[kdandekar]

Hi All,

Do we have the 'Reduce Data' with 'Keep Possible Values' functionality in Qlik Sense, like we had in QlikView  (anything apart from the ODAG option)?

or Do we have an API that will help us achieve this functionality?

We have a big application that we would like to split into multiple small apps based on custom Selected + Possible Values, any pointers would be helpful.

Ideally we would like to do this reduction on a scheduled basis (similar to the loop and reduce functionality)..

Thanks,

KD

[Anil_Babu_Samineni]

I don't see such so far, have a look https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Reduce_Data1.ht...

[Johannes_Twisk]

Yes, there is a valid option that only requires LOAD-script. PS. I posted this answer in another thread as well, but I think this could be the solution here as well...

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