Solved: Re: Multiple loads per day - Qlik Community

imrasyed · ‎2022-09-19

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.

RafaelBarrios · ‎2022-09-19

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,

View solution in original post

Or · ‎2022-09-19

Use a timestamp for your incremental reload, rather than just a date.

If you only add rows rather than updating or deleting rows, you should also be able to load distinct from the combined table to avoid duplication if preferred.

imrasyed · ‎2022-09-19

even if i use timestamp,the existing qvd will be overwirtten with the current timestamp.

its like appending to the existing qvd.

So the data is going to be for two loads.

Or · ‎2022-09-19

I'm confused as to how you are incremental reloading. Typically an incremental reload would only load data that is new from the previous run. If this is done with a timestamp, there's no reason for there to be an issue with multiple loads per day.

Table1:

Load Stuff

From DB.Table

Where RecordTimeStamp >= vLastReload; // Only records that are new

Concatenate

Load Stuff

From QVD;

Store Table1 Into SomeFile.qvd(qvd);

RafaelBarrios · ‎2022-09-19

hi @imrasyed

i think you should try partial reload.

https://help.qlik.com/en-US/sense/February2022/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPref...

you have to use add load and then merge to keep the lastest to an specific record.

if you dont want or cant use partial reload, you will need to use a resident load to load the concatenated table and then use Max(date) function with a group by and delete the previous table.

best

imrasyed · ‎2022-09-19

Next time you are loading SomeFile.qvd(qvd) it already contains the record from previous load of the day.

MendyS · ‎2022-09-19

Hi @imrasyed

It depends on a lot of stuff,

you can export the log file to understand better where the bottleneck of the loads?

Or · ‎2022-09-19

And why is that a problem? We're only loading records that are new since the last ReloadTime (which is a timestamp, not a date). If we didn't load the records from earlier today they would be missing entirely...?

imrasyed · ‎2022-09-19

Let me explain in detail.

DT:

9/9/2022 7:30

9/9/2022 18:30

Created a Qvd querying db on 9/9/2022 7:30.

when implementing incremental load we get max(loaded_date) from qvd = 9/9/2022 7:30

and extract data from db which is greater than 9/9/2022 7:30 which equals to 9/9/2022 18:30

and concatenate to the exisitng data which has 9/9/2022 7:30.

The resultant qvd has 9/9/2022 7:30 + 9/9/2022 18:30.

What i need is it should load 9/9/2022 7:30 and after the second load is done it shd show 9/9/2022 18:30.

Basically one load per day.

Hope this is clear.

imrasyed · ‎2022-09-19

Hi @RafaelBarrios

Thanks for the Solution,

Will give it a try but only concern I have is the reloading of the table and grouping which may be time consuming.

Is there any alternative without loading the table so that its optimal.I have like millions of records and all this stuff is done on db side but directly fetching from db is taking like 2hrs of time.

And the same data from qvd took like 5mins.So wanted to optimize it using incremental loads but stuck at this multiple loads per day thing.