Multiple loads per day

[imrasyed]

Hi Experts,

I have historical data from DB and its taking lot of time to load it in Qliksense.

To resolve that issue I am using incremental load.

The issue i am facing is i have multiple loads per day.

And when i am getting max date and concatenating with the existing qvd i will be getting multiple loads.

But instead i need only the latest loaded data per day.

Example:

loaded_dt:

9/10/2022 7:10

9/10/2022 18:30

---

---

9/19/2022 7:10

9/19/2022 18:30

What i need is 

9/10/2022 18:30

9/11/2022 18:30

---

---

9/19/2022 18:30

Is it possible to acheive this using incremental load?

Appreciate your help.

[Or]

I don't understand why. You have two lines, why would you expect your QVD to only show one of them?

If this is a case of a line being modified, you should be running with some sort of Exists or Not Exists function checking to see if the line already exists, but I can't guess at what since there's no additional information.

[RafaelBarrios · Accepted solution]

Hi @imrasyed 

so, your best option is Partial Reload

it will not load again the previous records, only the new ones with the where date you already retrieve

//PREVIOUSLY LOADED MAIN_TABLE AS NORMAL LOAD

//IN NEXT PARTIAL RELOAD

NEW_RECORDS:

add only Load

if(exists(record_id),'Update','Insert') as OrdersOperation,

record_id,

date_field as DateField,

any_field,

any_other_fields

from <where ever you are loading>

WHERE date_field> $(vRetrievedDate);

Merge only (DateField, LastDateField) on record_id Concatenate(MAIN_TABLE)
LOAD

   orders_operation as Operation,

   DateField,

   record_id,

   any_field,

   any_other_fields

RESIDENT NEW_RECORDS;

//add a condition to ask if it is a partial load or the drop will fail on normal load

DROP TABLE NEW_RECORDS;

it may have a syntax error because I haven't tried it, but it's the idea and I've done it before for something similar.

Best,

[imrasyed]

Never used partial load but how is it going to load the latest load per day.

If you don't mind can you please brief.

Appreciate your help.

[marcus_sommer]

It's not really clear how your data and your update-process are look like. Without a clear logic it's difficult to find a proper approach. At first you need to elaborate how the data-base content might changing:

• only new records
• existing records with some changes
• deleted records

The first one - that there are always only new records - is the most common one and could be quite simply solved with the above already mentioned concatenate-loads and exists-clauses. If to the first also comes the second and/or the third one it could become quite difficult and you will need in each case more efforts and of course more information how these changed and/or deleted records could be detect. These data might be for an example an additionally change-timestamp and/or state-information or a counter-value and/or similar stuff - always in regard to the key-fields of the records. Such information should be available within the data-base. If not you could try to create own ones but this could become very difficult.

In many scenarios the complex approaches for updating/deleting methods within the incremental logic could be skipped by relinquishing some load-performance, for example by always loading the n last days from the data-base and all previous data came from the qvd - and one nightly load-run adds the oldest current day to the qvd and the next day it starts again from the beginning. Yes, the database may now not return a few hundreds/thousands of records else a few ten thousands records and the rest of n millions records came from the qvd but often this lost could be neglected.

- Marcus

[imrasyed]

Hi @marcus_sommer ,

I have a simple table with million records for each load and its a truncate load in DB.

Here i need to append the data on Qlikside for rolling 30 days.Its simply insert of million records for every load and i have a timestamp column.

Problem is i have two loads per day with million records each , but i need only the latest load of the day to be inserted in the table.

[imrasyed]

Thanks @RafaelBarrios 

 Looks like partial reload isnt available in Enterprise version Feb 2021 version.

Will go with group by logic what you provided.

Thanks again.

[marcus_sommer]

It's further not clear how your data look like and what do you want to do? Is really only the single field timestamp is relevant you could apply the very simple logic provided above from @Or. If the timestamp is instead just an information which are related to further key-fields it becomes more complex to identify the records.

Like above mentioned you may be able to skip some of the complexity. Beside the suggestions there how long does such last 30 days query run? Maybe such load might be divided into n parts and also running in parallel and/or in other time-frames. Yes, it may create some extra overhead but extending the environment to a multi-tier data-architecture and if it already exists with some more parallel or deeper layers is in larger environments very often useful. A slow responding data-base or a slow network may not be mandatory a showstopper to pull larger data-sets.

- Marcus

[RafaelBarrios]

hi @imrasyed 

Thats true... to trigger a partial reload from the QMC is available from FEB2022.

in Feb2021 is available only from the App with a button or using the API.

the Group By should do the trick

You could also try:

First load the new records.

Then load the full History QVD with an exists clause

Load

     Key1,

     fields

from <where ever you load>;

concatenate

LOAD

     Key2 as Key1

     Fields

FROM <previous saved qvd>

WHERE NOT EXISTS(Key1, Key2) ;

The first parameter should be a previously existing field, and the second a field or expression in the current load.

Hope it works for you.

Best,

[imrasyed]

DBTablefirst_run_of_the_day:

lddate,   rid,   pluid,  scandt ....... 70 cols

9/10/2022 7:30,r1,p1,9/10/2022  .....

9/10/2022 7:30,r2,p1,9/10/2022  .....

9/10/2022 7:30,r3,p1,9/10/2022  .....

9/10/2022 7:30,r4,p2,9/10/2022  .....

---

---

On the next run DBTablefirst_run_of_the_day gets truncated and gets loaded with below table.

DBTablesecond_run_of_the_day:

lddate,   rid,   pluid,  scandt ....... 70 cols

9/10/2022 10:30,r1,p1,9/10/2022  .....

9/10/2022 10:30,r2,p1,9/10/2022  .....

9/10/2022 10:30,r3,p1,9/10/2022  .....

9/10/2022 10:30,r4,p2,9/10/2022  .....

---

---

On Qlikside :

I will have to append it to a qvd but the latest load of the day.

Before 9/10/2022 10:30 load my qvd shd contain 9/10/2022 7:30.And after 9/10/2022 10:30 my qvd shd hold only 9/10/2022 10:30 data.

similar with upcoming days like 9/11/2022,9/12/2022 and so on.

Hope its clear now.

[marcus_sommer]

It's further not really clear how the data are changing over the day. I would now interpret your last comment that there are no data changes to track else the data-base pushed all n hours the actual data of the running day - surely there are always more or less new ones and some older might have changed and/or be removed but this mustn't be.

In this case you need to store only the last query of the day within the historic.qvd. There is no need to add the data each time and removing the previous ones again. If you want the daily data as qvd you could overwrite a daily.qvd each time or creating a new one with a timestamp-information within the file-name.

- Marcus