Partial Reload issues

[andrea95]

Hello.

We are building an app (using the Cloud SaaS version) with the following characteristics:

- we have an input dashboard in which the user select one or more fields from the available dimensions in the Qlik Sense app and then click a trigger button passing as parameters the current state of the selection.

- the button triggers an automation that writes the log in our Postgres DB where an elaboration takes place

- finally our elaboration store the data related to the elaboration in a Postgres table along with a SESSION ID and the USER ID (user id on Qlik Sense) that made the request.

- after completion our script calls via POST a Qlik Automation that reload the data on our app so that the user can see the result of the elaboration.

- at the moment in the loading script we load the latest 5 elaboration made by users and give the user the possibility to switch from one elaboration to another one (workaround to avoid different users overriding their elaboration).

Now, we want to optimize loading times since a FULL Load takes 1 minute to load all the latest 5 elaboration + the available dimensions. 

For this reason we tried to implement the Partial Reload. Idea was:

- every day make a full reload with the latest 5 elaboration

- every time the user SUBMITS a new request it triggers only a partial reload that loads only the latest session.

We're currently having an issue though with consecutive partial reload, for instance the first partial reload is working fine adding the newest session to the latest 5 (loaded during the FULL), but the consecutive partial reloads will load the newest session keeping the 5 sessions loaded during the daily full but LOSING the other partial reloads performed during the day.
Example: during full sessions 1,2,3,4,5 are loaded. Then first user submit a requests, on qlik I find the data for sessions 1,2,3,4,5 and 6. When another user submits, the latest is loaded and on qlik i find sessions 1,2,3,4,5 and 7 (session 6 is lost).

We currently don't understand if this is an error in the script or if the functionality is supposed to work like this. Could you please give advice on how to achieve the intended behaviour?

This is the Script:

LIB CONNECT TO 'connector';

// Full Reload: load all sessions
IF NOT IsPartialReload() THEN
  Forecast:
  LOAD
      data...  ;
  // Load data directly from postgres DB Table
  SQL SELECT
      data...;
  FROM table_name
  WHERE session_id >= (SELECT MAX(session_id) - 3  from table_name);

END IF

// Partial Reload case
IF IsPartialReload() THEN
    ADD LOAD
        data... ;
    SQL SELECT
        data... ;
    FROM table_name
    WHERE session_id = (
        SELECT MAX(session_id)
        FROM table_name
    );

END IF

Thanks in advance

Andrea

[SRA]

Hi,

It should work as you want. 

I did one exemple:

IF NOT IsPartialReload() THEN

Data:

LOAD

    Session,

    Value,

    Now() as ReloadTime

FROM [lib://DataFiles/PartialReload.xlsx](ooxml, embedded labels, table is Feuil1);

Else

Data:

Add Load

    Session,

    Value,

    Now() as ReloadTime

FROM [lib://DataFiles/PartialReload.xlsx](ooxml, embedded labels, table is Feuil1)

Where Session > '$(sMaxSession)';

Endif;

T_Max:

Add Load

MaxString(Session) as Tmp_MaxSession

Resident Data;

Let sMaxSession = Peek('Tmp_MaxSession');

Trace sMaxSession = $(sMaxSession);

With the first FullReload, I got Sessions A, B & C. Then I added D & E and ran a first partial reload, then I added F & G and ran a second partial reload. You can see for each reload the corresponding time...

Regards 

SRA