incremental load, mistake in concatenate/ order?

[QlikSenseUser1]

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.wrong 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);

[lironbaram]

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]

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);

[jonathandienst]

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

[QlikSenseUser1]

Hi  Jontydkpi, 

I understand that its not really necessary as the structure is irrelevant once loaded into a qvf. 
It's rather for troubleshooting purposes to see if the load process is working correctly ( I guess its more efficient to implement checks within qvf files/dashboards? 

The reason I'm asking: 
This is a sample template which will be extended to work with a large amount of data where I'm trying to optimize the load speed. 
Does sorting the qvd significantly affect the incremental upload procedure? 

Thank you for your insights!

[jonathandienst · Accepted solution]

>>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.

[QlikSenseUser1]

Understood, thanks for your help!