Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Black_Hole
Contributor

Load data from QVD/QVS in QVW

Hello All,

I tried, for the first time, to realize an incremental load of the data.

As a newbie in QlikView, I took this topic as main support in order to be able to have a first version of the incremental load.

 In total, I stored two qvs files and one qvd file during the process of the incremental load.

Now, the next step is to build my report (like an user interface).

But, I don't know what file I should take to load all the data (i.e. the history and the new data) ?

Please could you help me to understand thanks to the link above.

Thank you in advance for your help.

Labels (4)
1 Solution

Accepted Solutions
Partner
Partner

Re: Load data from QVD/QVS in QVW

Hi,

in .qvs you only store script.

So I think in qvs1 you store your "old" datas and then you never need this.

After that you load your new datas and store this script into qvs2.

 

For incremental load you 

1. load your actual datas from datasource

2. add existing datas from your .qvd (but not existing numbers from your datasource)

3. store all datas into your qvd

With this type of incremental you load actual datas from datasource add old datas and store them. With " existing numbers" I mean eg order number and line because only this combination detect an individual line.

 

Also you can load datas in loops

load

your datas from your source where year = year (today())

store these datas into file_year (eg file_2019)

and then loop with year -1 and store datas into year-1 (eg file_2018)

This loop you can do if lines never been corrected eg invoices. By law, an invoice is corrected by another invoice with negative values, but never an existing line.

Then you can load * from file_*.qvd into your datamodel to load all datas from all years.

There are many topics for this theme in Qommunity.

Regards

8 Replies
Partner
Partner

Re: Load data from QVD/QVS in QVW

Hi,

if you scripted like in the marked topic, there is only one file of data ,it's the .qvd

New datas are loaded from your source, existsting datas from the existing qvd are concatenated (attached) to the datas and stored into the qvd again.

.qvs is only a script file.

Regards

Black_Hole
Contributor

Re: Load data from QVD/QVS in QVW

Hi @martinpohl ,

Thank you very much for your reply.

So, to load the data I will use the qvd, I understand.

But, there is something that I'm still confused.

I would like to know at which moment, we will use the qvs.

I don't see clearly how the part  with concatenation is used in the process (it's the file named increm2 in the topic).

Please could you help me to understand the different steps of the incremental load.

 

I hope that my request is clearly formulated.

Thank you in advance for your help.

Partner
Partner

Re: Load data from QVD/QVS in QVW

Hi,

in .qvs you only store script.

So I think in qvs1 you store your "old" datas and then you never need this.

After that you load your new datas and store this script into qvs2.

 

For incremental load you 

1. load your actual datas from datasource

2. add existing datas from your .qvd (but not existing numbers from your datasource)

3. store all datas into your qvd

With this type of incremental you load actual datas from datasource add old datas and store them. With " existing numbers" I mean eg order number and line because only this combination detect an individual line.

 

Also you can load datas in loops

load

your datas from your source where year = year (today())

store these datas into file_year (eg file_2019)

and then loop with year -1 and store datas into year-1 (eg file_2018)

This loop you can do if lines never been corrected eg invoices. By law, an invoice is corrected by another invoice with negative values, but never an existing line.

Then you can load * from file_*.qvd into your datamodel to load all datas from all years.

There are many topics for this theme in Qommunity.

Regards

Black_Hole
Contributor

Re: Load data from QVD/QVS in QVW

Hi @martinpohl ,

Thank you very much for this detailed explanation.

I understand better the process of the incremental load.

I have a last question about this subject.

If I want to join two massive tables.

The best practice and optimum way to join these tables:

Is it to join these two tables in the same qvd ?

Or to create two incremental loads for the two tables (so two qvd files) and another qvd file to join these two tables?

Please could you advise me and if it is possible to share an example.

Thanks a lot for your help.

Partner
Partner

Re: Load data from QVD/QVS in QVW

Hi,

I would load both tables in different files and join them in Qlik.

But be careful if you try this in Qlik:

load * from header_*.qvd;

left join load * from lines_*.qvd;

in some versions you only will get a join result for the first line-qvd that is load.

better do this:

temp:

load * from header_*.qvd;

lines:

load * from lines_*.qvd;

left join (header) load * resident lines;

drop table lines;

Regards

Black_Hole
Contributor

Re: Load data from QVD/QVS in QVW

Hi @martinpohl ,

Thank for your reply.

I tried it. But, I have an error message 

"Table not found error

Table 'ID' not found

INNER JOIN(ID) LOAD * FROM TEMP"

Below my script:

MAIN:
LOAD * FROM $(QVDPath)Incr1.qvd (qvd);

TEMP:
LOAD * FROM $(QVDPath)Incr2.qvd (qvd);
INNER JOIN(ID) LOAD * FROM TEMP;

Please you help me how I can correct this issue.

Thank you in advance for your help.

Highlighted
Partner
Partner

Re: Load data from QVD/QVS in QVW

Hi,

change this line

INNER JOIN(ID) LOAD * FROM TEMP;

to 

INNER JOIN(MAIN) LOAD * FROM TEMP;

The value in () is not the field it is joined by, it is the table the datas are joined to,

The fields which are joined are like in a datamodel all fields in both tables with the same fieldname.

Regards

Black_Hole
Contributor

Re: Load data from QVD/QVS in QVW

Hello @martinpohl ,

I changed it as you suggested above and the result is correct now.

Thank you very much for your help.

Kind regards