Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

andrefpc
Contributor

Reload Structure

Good afternoon,

I have a project in which our reload structure is like this:

-everyday a daily qvd is generated.

-everyday a monthly qvd is generated (with the qvds from all days)

-everyday a yearly qvd is generated (with the qvds from all months)

Sometimes new fields are added to the DB, which will cause inconsistencies between the qvds (for example if 2016 has field A and 2015 hasn't it will create a new table-1 while reading).

What is the best practice and faster way to update every qvd without having to re-read every single day from the DB?

Thanks in advance.

1 Solution

Accepted Solutions

Re: Reload Structure

Several answers depending on your scenario.

1. FieldA exists in 2016 but not 2015.

- Use the Concatenate() keyword when loading QVDs to ensure a single table.  FieldA will be null for the 2015 rows.

2. FieldA was added in 2016 and your DBA has populated meaningful values for FieldA in 2015 rows.

- Select FieldA and the PrimaryKey from 2015 and join to the 2015 QVD.

2015Data:

LOAD * FROM 2015.qvd;

LEFT JOIN(2015Data)

SQL SELECT KeyField, FieldA from table;

STORE 2015Data INTO 2015.qvd;

2. FieldA was added in 2016 and your DBA has populated a default value for FieldA in 2015 rows.

- Join a Default value to each 2015 row.

2015Data:

LOAD * FROM 2015.qvd;

LEFT JOIN(2015Data)

LOAD 'Default' as FieldA AUTOGENERATE 1;

STORE 2015Data INTO 2015.qvd;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

4 Replies
vinieme12
Esteemed Contributor II

Re: Reload Structure

Incremental load,  search the term on community..

Just keep the yearly one

Re: Reload Structure

Several answers depending on your scenario.

1. FieldA exists in 2016 but not 2015.

- Use the Concatenate() keyword when loading QVDs to ensure a single table.  FieldA will be null for the 2015 rows.

2. FieldA was added in 2016 and your DBA has populated meaningful values for FieldA in 2015 rows.

- Select FieldA and the PrimaryKey from 2015 and join to the 2015 QVD.

2015Data:

LOAD * FROM 2015.qvd;

LEFT JOIN(2015Data)

SQL SELECT KeyField, FieldA from table;

STORE 2015Data INTO 2015.qvd;

2. FieldA was added in 2016 and your DBA has populated a default value for FieldA in 2015 rows.

- Join a Default value to each 2015 row.

2015Data:

LOAD * FROM 2015.qvd;

LEFT JOIN(2015Data)

LOAD 'Default' as FieldA AUTOGENERATE 1;

STORE 2015Data INTO 2015.qvd;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

andrefpc
Contributor

Re: Reload Structure

Thanks for your answers guys.

One more thing,

Let's say we have data from 2015 and 2016, what's the difference between:

Load * from 20*.qvd

and

Load * from 2015.qvd

Concatenate(2015)

Load * from 2016.qvd

Once again, thank you.

Re: Reload Structure

Load * from 20*.qvd


will produce a single QV table only if all files have the same fields (automatic concatenation).


Load * from 2015.qvd

Concatenate(2015)

Load * from 2016.qvd

Will produce a single table, even if the fields are different (forced concatenation).

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Community Browser