topic Re: Load data into a temporary table in QlikView

Load data into a temporary table

harson — Wed, 25 Nov 2020 16:16:04 GMT

Hello Community,

I need help.

My situation :

I have several rows of data for the years 2015,2016 and 2017 in my database while my QlikView report must output a graph every day with the existing data (from 2015 to 2017)
My problem is that loading data from 2015 to 2017 takes a long time.
What I would like to do then, if possible on QlikView, is to load the data from January 2015 to the previous month of the current month for 2017 early in the month.

For example :
On 1 May 2017, I load a temporary table containing the data from 01 January 2015 to 30 April 2017.
Then during the month of May, for my report, I will only reload the data of the month of May, the remains I take them just in my temporary table to avoid reloading the data from 01 Jan 2015 to May 2017 (max date), Since the data from 01 January 2015 to 30 April 2017 no longer change, they are no longer modifiable when we move to a new month.

Thanks in advance for your help.

Tsiry

Re: Load data into a temporary table

Frank_Hartmann — Tue, 02 May 2017 06:41:13 GMT

You will need to perform an incremental load

https://community.qlik.com/search.jspa?q=incremental+load&type=document

QVD (QlikView Data File)

Incremental Load.docx

https://www.quickintelligence.co.uk/qlikview-incremental-load/

hope this helps

Re: Load data into a temporary table

ankitaag — Tue, 02 May 2017 06:59:36 GMT

Hi Tsiry,

You can first save your historical data in one QVD (HistoryData) and make a separate QVD when fetching current month's data (Current Data).

This way your history remains constant always and just the current month's data will change. Then later concatenate the two QVDs and override the HistoryData qvd.

For ex-

vCurrentMonth=May 2017;

CurrData:

Load *

From table

where MonthYear='$(vCurrentMonth)';

HistoryData:

Load *

From HistoryData

where MonthYear<>'$(vCurrentMonth)';

Store CurrData into [path\HistoryData.qvd] (qvd);

The above condition in 2nd load "'where MonthYear<>'$(vCurrentMonth)';" is required so that when you concatenate and store the data, current month's data doesn't get duplicate.

Hope this helps!

Thanks and Regards,

Ankita

Re: Load data into a temporary table

rahulpawarb — Tue, 02 May 2017 07:02:27 GMT

Hello Tsiry,

Trust that you are doing great!

Please refer below given sample script:

LET vLastExecTime =Date(MonthStart(Today())-1);


Data:
LOAD Id,
...
FROM <TableName>
WHERE LastModifiedDate > $(vLastExecTime);


Concatenate 

History:
LOAD Id,
...
FROM $(vQVDPath)<TableName>.qvd
WHERE NOT EXISTS(Id);

Hope this will be helpful.

Regards!

Rahul