Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
QlikSenseUser1
New Contributor III

incremental load, mistake in concatenate/ order?

Hi, 

 

This is a routine w

The code works fine now, but I just realized that the tables are joined such that the new "incrementally loaded data" (ie. the newest data) is placed at the top of the QVD file and the old data follows below. 

IE: if I head a timseries expressed as integers my table would start with 16, 17.....to 25 and AFTER THAT start from 1, 2, ..to 15.Order.PNGwrong order in QVD file

 
Is there a way so that it saves it in the right order in the qvd? 
(I suspect a mistake in the way I concatenated both tables?)

THANKS SO MUCH!

Please find below the otherwise working code: 

 

// 1 .File load
table1:
LOAD id,
date,
[firsr name],
[second name]
FROM
'lib://Incremental Load/incr1.xlsx'
(ooxml, embedded labels, table is Sheet1);

//2. Create a qvd file.
store table1 into 'lib://QVD/Test.qvd';
drop table table1;

//3. Find current maximum date
max_date:
LOAD
max(date) as Maxdate
FROM 'lib://QVD/Test.qvd'(qvd);

//4. Store the Maximum date in a variable.
Let Maxdate = floor(peek('Maxdate'));

//5.Pull new rows from `incr2.xlsx`.
stored_new:
NoConcatenate
LOAD id,
date,
[firsr name],
[second name]
FROM
[lib://Incremental Load/incr2.xlsx]
(ooxml, embedded labels, table is Sheet1)
where date> $(Maxdate);
//STORE stored_new into [lib://QVD/Testdelta.qvd] (qvd);

//6 Concatenate.
Concatenate (stored_new)
LOAD id,
date,
[firsr name],
[second name]
FROM [lib://QVD/Test.qvd] (qvd);
STORE stored_new into [lib://QVD/Test.qvd] (qvd);

Labels (3)
1 Solution

Accepted Solutions
MVP
MVP

Re: incremental load, mistake in concatenate/ order?

>>Does sorting the qvd significantly affect the incremental upload procedure? 

in general, it will make no difference. The major difference is an (unnecessary?) complication of the incremental load process - so unless there is a specific need, I would not do this. Perform the QVD checks in QV or QS.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
6 Replies
Partner
Partner

Re: incremental load, mistake in concatenate/ order?

hello 

you can change your script to work as per below 

// 1 .File load
table1:
LOAD id,
date,
[firsr name],
[second name]
FROM
'lib://Incremental Load/incr1.xlsx'
(ooxml, embedded labels, table is Sheet1);

//2. Create a qvd file.
store table1 into 'lib://QVD/Test.qvd';
drop table table1;

//3. load current data 

stored_new:

LOAD id,
date,
[firsr name],
[second name]
FROM [lib://QVD/Test.qvd] (qvd);

4.//find max date 
max_date:
LOAD
max(date) as Maxdate
FROM 'lib://QVD/Test.qvd'(qvd);

//5. Store the Maximum date in a variable.
Let Maxdate = floor(peek('Maxdate'));

//6.Pull new rows from `incr2.xlsx`.

Concatenate (stored_new)
LOAD id,
date,
[firsr name],
[second name]
FROM
[lib://Incremental Load/incr2.xlsx]
(ooxml, embedded labels, table is Sheet1)
where date> $(Maxdate);

STORE stored_new into [lib://QVD/Test.qvd] (qvd);

QlikSenseUser1
New Contributor III

Re: incremental load, mistake in concatenate/ order?

Thanks for the quick reply! Unfortunately, its not working for me, error message:

"The following error occurred:
Field 'a' not found
The error occurred here:
?"


From what I can tell, you have changed the order (did I miss something) ? 

I have slightly adjusted your code as there was a / missing before the fourth step. This is what I worked with: 


// 1 .File load
table1:
LOAD id,
date,
[firsr name],
[second name]
FROM
'lib://Incremental Load/incr1.xlsx'
(ooxml, embedded labels, table is Sheet1);

//2. Create a qvd file.
store table1 into 'lib://QVD/Test.qvd';
drop table table1;

//3. load current data
stored_new:
LOAD id,
date,
[firsr name],
[second name]
FROM [lib://QVD/Test.qvd] (qvd);

//4. find max date
max_date:
LOAD
max(date) as Maxdate
FROM 'lib://QVD/Test.qvd'(qvd);

//5. Store the Maximum date in a variable.
Let Maxdate = floor(peek('Maxdate'));

//6.Pull new rows from `incr2.xlsx`.
Concatenate (stored_new)
LOAD id,
date,
[firsr name],
[second name]
FROM
[lib://Incremental Load/incr2.xlsx]
(ooxml, embedded labels, table is Sheet1)
where date> $(Maxdate);

STORE stored_new into [lib://QVD/Test.qvd] (qvd);

MVP
MVP

Re: incremental load, mistake in concatenate/ order?

Is there a specific reason that you want the QVD sorted? 

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
QlikSenseUser1
New Contributor III

Re: incremental load, mistake in concatenate/ order?

Hi  

MVP
MVP

Re: incremental load, mistake in concatenate/ order?

>>Does sorting the qvd significantly affect the incremental upload procedure? 

in general, it will make no difference. The major difference is an (unnecessary?) complication of the incremental load process - so unless there is a specific need, I would not do this. Perform the QVD checks in QV or QS.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
QlikSenseUser1
New Contributor III

Re: incremental load, mistake in concatenate/ order?

Understood, thanks for your help!