Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
JoannaM
Contributor III
Contributor III

Store to data / how to shorten loading time

Hi all

I have created an APP where I store the daily stock. This is my stock history data.

I am pulling this data into another app but the laoding time is getting longer and longer every day since every day there is a new line to load. It also takes a lot of time to load the tables and charts in the dashboard.

Is there a way to shorten the data load?

 

My load:

LOAD*

FROM [lib://QlikSenseShare/10_Shared/30_Prod/10_SAP/300_QVD_Extract/0326_MM_InventoryManagement/Mod/Stock_store/Stock_store*.qvd]
(qvd)WHERE batch<>''and Date(Floor("store date"),'DD.MM.YYYY')>=Addmonths(today(1),-24);

 

Thanks a lot.

Best regards,

Joanna

1 Solution

Accepted Solutions
marcus_sommer

There is a small bug in the exist-table because it should generate dates before two years to today and not from today in the future. Therefore change it to:

[DateForExists]: load date(floor(addmonths(today(1), -24) - 1 + recno())) as [store date]
autogenerate today(1) - addmonths(today(1), -24);

- Marcus

View solution in original post

3 Replies
marcus_sommer

Very important to get a good load-performance is to ensure that the qvd's are within the optimized mode. This means each file must have the same data-structure and there are no measures included which require a processing of the data - to make it short: only a renaming of fields and a where exists-clause with a single parameter are allowed to keep a load optimized.

In your case it means you need to adjust the where-condition - for the date-part this might be done with something like this:

[DateForExists]: load date(floor(today(1) - 1 + recno())) as [store date]
autogenerate today(1) - addmonths(today(1), -24);

and then loading from the qvd with:

load ... where exists([store date]);

If there is more as a single condition the multiple conditions must be merged in beforehand. In your case it could mean to create an extra field within the routine which creates the qvd maybe with something like this:

if(batch<>"",  [store date], null()) as  [store date for exists]

Another essential point is only to access the relevant qvd's. This means if there are daily files from the last 20 years your wildcard-load will access each file and checking the date. Means it touched 20 years, checked them, skipped 18 years and loads finally 2 years. Better then this is to access only the wanted 2 years and this could be done with a filelist() loop, like:

for each file in filelist('YourPathWithFilePattern.qvd')
   if keepchar(subfield('$(file)', '\', -1), '0123456789') >= addmonths(today(1), -24) then
      load ...
   end if
next

This requires of course that the date/period information is included within the filename which is often the case and if not you should consider to adjust them appropriate. Depending of your final pattern you may need a few adjustments to the format- and/or picking-logic but it's not difficult and could be easily tried within a few text-boxes.

Beside this you should only load those fields and records which are really needed - means no record-id's and if there are timestamp fields they should be divided into a date and a time field (of course already in beforehand by creating the qvd). 

- Marcus

marcus_sommer

There is a small bug in the exist-table because it should generate dates before two years to today and not from today in the future. Therefore change it to:

[DateForExists]: load date(floor(addmonths(today(1), -24) - 1 + recno())) as [store date]
autogenerate today(1) - addmonths(today(1), -24);

- Marcus

JoannaM
Contributor III
Contributor III
Author

Hi Marcus

Thank you so much for the help. I used the for loop and I could optimize my performance. Unfortunalely, I can't change the store to load anymore but I will keep your suggestions in mind for next projects.

BR, Joanna