Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

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

Highlighted
MVP
MVP

Drop sample table before IncTable, like:

......

Drop Table Sample;

IncTable:

LOAD Number,

          Alphabet

....

Highlighted
Creator III
Creator III

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

Highlighted
Creator III
Creator III

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

Highlighted
Not applicable

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

Highlighted
MVP
MVP

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

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

Highlighted
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

Highlighted
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

Highlighted
Creator III
Creator III

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