Incremental Load without Updated Date and Time

[Anonymous]

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

Number	Alphabet
1	A
2	B
3	C
4	D

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

Number	Alphabet
1	A
2	B
3	C
4	D
5	E

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

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

[Ralf-Narfeldt]

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]

Drop sample table before IncTable, like:

......

Drop Table Sample;

IncTable:

LOAD Number,

          Alphabet

....

[Anonymous]

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

[Anonymous]

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

[#6]

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]

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

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

[sunilkumarqv · Accepted solution]

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

Number	Alphabet
1	A
2	B
3	C
4	D
5	E

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]

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]

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