Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Incremental Load without Updated Date and Time

The file I have for the Initial load is Sample.xlsx

NumberAlphabet
1A
2B
3C
4D

After loading this file, I'm storing it as a QVD File using the following Code,

Sample:

LOAD Number,

          Alphabet

FROM Sample.xlsx (ooxml, embedded labels, table is Sheet1);

Store Sample into Sample.qvd(qvd);

I have inserted a new row to the Sample.xlsx as

NumberAlphabet
1A
2B
3C
4D
5E

For performing Incremental Load (Insert and Update) on this data, I have added the following code to the existing Code.

MaxNumber:

LOAD max(Number) as MaxNumber

Resident Sample;

Let vmax=peek('MaxNumber',0,'Last_Updated_Number');

Drop Table MaxNumber;

IncTable:

LOAD Number,

          Alphabet

FROM Sample.xlsx

(ooxml, embedded labels, table is Sheet1) where Number > $(vmax);

Concatenate

LOAD Number,

          Alphabet

FROM Sample.qvd (qvd) where not Exists(Number);

Drop Table IncTable;

Once I reload this, I'm encountering a problem like

Capture.PNG

What is even more baffling is, I got the updated data when I checked the Preview of the Table in Data model.

Can someone help me on this? Where I was wrong and What needs to be done to get this without any Script Error?

Thanks

1 Solution

Accepted Solutions
sunilkumarqv
Specialist II
Specialist II

I changed your script based on your requirement

Sample:

LOAD Number,

          Alphabet

FROM Sample.xlsx (ooxml, embedded labels, table is Sheet1);

Store Sample into Sample.qvd(qvd);

I have inserted a new row to the Sample.xlsx as

NumberAlphabet
1A
2B
3C
4D
5E

For performing Incremental Load (Insert and Update) on this data, I have added the following code to the existing Code.

MaxNumber:

LOAD max(Number) as Last_Updated_Number

Resident Sample;

Let vmax=peek('Last_Updated_Number',0,'MaxNumber');

Drop Table MaxNumber;

Drop Table Sample;

IncTable:

LOAD Number,

          Alphabet

FROM Sample.xlsx

(ooxml, embedded labels, table is Sheet1) where Number > $(vmax);

Concatenate

LOAD Number,

          Alphabet

FROM Sample.qvd (qvd) where not Exists(Number);

View solution in original post

12 Replies
Ralf-Narfeldt
Employee
Employee

You are still loading the Sample: table at the start, right?

Then the load in IncTable automatically concatenates, and as IncTable is not created you can't drop it.

tresesco
MVP
MVP

Drop sample table before IncTable, like:

......

Drop Table Sample;

IncTable:

LOAD Number,

          Alphabet

....

Anonymous
Not applicable
Author

Thanks for the help. If I drop the Sample Table, I'm not getting any table loaded into Qlikview.

Anonymous
Not applicable
Author

If I do that, no table is loaded into the Qlikview.

Not applicable
Author

Hi

    Here am not understanding One thing

  why you are trying to drop the IncTable. if we are drop the IncTable then there so no data to load in to QVD

    Correct me if my understanding wrong

suggestion:comment the drop table command

tresesco
MVP
MVP

You need one more correction, remove the wrong table name from peek().

Let vmax=peek('MaxNumber',0,'MaxNumber');

sunilkumarqv
Specialist II
Specialist II

I changed your script based on your requirement

Sample:

LOAD Number,

          Alphabet

FROM Sample.xlsx (ooxml, embedded labels, table is Sheet1);

Store Sample into Sample.qvd(qvd);

I have inserted a new row to the Sample.xlsx as

NumberAlphabet
1A
2B
3C
4D
5E

For performing Incremental Load (Insert and Update) on this data, I have added the following code to the existing Code.

MaxNumber:

LOAD max(Number) as Last_Updated_Number

Resident Sample;

Let vmax=peek('Last_Updated_Number',0,'MaxNumber');

Drop Table MaxNumber;

Drop Table Sample;

IncTable:

LOAD Number,

          Alphabet

FROM Sample.xlsx

(ooxml, embedded labels, table is Sheet1) where Number > $(vmax);

Concatenate

LOAD Number,

          Alphabet

FROM Sample.qvd (qvd) where not Exists(Number);

Ralf-Narfeldt
Employee
Employee

I think you should change your initial load to be from the QVD instead of XLSX, and then put the Store at the end of the script.

Load from QVD

Calculate MaxNumber and store to variable

Drop MaxNumber

Load from XLSX where Number > $(vmax);

Store to QVD

Anonymous
Not applicable
Author

Ah. What a Catastrophic mistake have I done! Thanks for helping out mate. Now it works properly.